Comparing data
Closed
Surabhee
Posts
1
Registration date
Saturday February 14, 2015
Status
Member
Last seen
February 14, 2015
-
Feb 14, 2015 at 06:52 AM
mattgdunn Posts 4 Registration date Thursday June 2, 2016 Status Member Last seen June 2, 2016 - Jun 2, 2016 at 08:19 PM
mattgdunn Posts 4 Registration date Thursday June 2, 2016 Status Member Last seen June 2, 2016 - Jun 2, 2016 at 08:19 PM
Related:
- Comparing data
- Tmobile data check - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to be offline in whatsapp when data is on - Guide
- Data transmission cable - Guide
- Spectrum data usage - Guide
1 response
mattgdunn
Posts
4
Registration date
Thursday June 2, 2016
Status
Member
Last seen
June 2, 2016
Jun 2, 2016 at 08:19 PM
Jun 2, 2016 at 08:19 PM
Hi Surabhee,
This example from excelvlookuphelp.com looks at exactly the same situation as you have:
You might have a new list of prices from a supplier of products which you’d like to compare to another price list (your existing prices for example). The only way of linking them may be their product numbers. Rather than using the Find function again and again, you could use a vlookup. Follow along in the Price Benchmarking Tutorial Workbook.
We have the two tables on one worksheet for this example. The first is the list of prices that you’ve just received from a supplier. The second is the other pricing that you’re going to use to benchmark with.
Table 1
Table 2
So using the standard structure of =vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact ) where
SearchFor is the product number (cell A4)
WhereToSearch is the list of benchmarks in column G and H (G:H)
Which column is the second column (2)
NearOrExact is Exact (False)
Our formula looks like =vlookup(A4, G:H, 2, FALSE)
price benchmarking vlookup formula
Excellent! So now we have the first benchmark price in there we just drag the column down to get the others.
To compare them we can add in a second formula in column E to show the extent of the difference. We use [Old Price] minus [New Price] and divide the result by the [Old Price] as follows:
And there we have it, a table showing where better pricing is available in our benchmarks (all the negative percentages in column E).
In a negotiation with your potential supplier you could take just the products where the prices are better than the ones that they offered in our original table 1 and ask them to match the better price so that you have the lowest known price from the combination of the two price lists.
This technique can be used for comparing any two price lists. For example you might publish your own price list on a regular basis and need to use a vlookup to put the new prices into the existing price list.
Source: www.excelvlookuphelp.com
This example from excelvlookuphelp.com looks at exactly the same situation as you have:
You might have a new list of prices from a supplier of products which you’d like to compare to another price list (your existing prices for example). The only way of linking them may be their product numbers. Rather than using the Find function again and again, you could use a vlookup. Follow along in the Price Benchmarking Tutorial Workbook.
We have the two tables on one worksheet for this example. The first is the list of prices that you’ve just received from a supplier. The second is the other pricing that you’re going to use to benchmark with.
Table 1
Table 2
So using the standard structure of =vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact ) where
SearchFor is the product number (cell A4)
WhereToSearch is the list of benchmarks in column G and H (G:H)
Which column is the second column (2)
NearOrExact is Exact (False)
Our formula looks like =vlookup(A4, G:H, 2, FALSE)
price benchmarking vlookup formula
Excellent! So now we have the first benchmark price in there we just drag the column down to get the others.
To compare them we can add in a second formula in column E to show the extent of the difference. We use [Old Price] minus [New Price] and divide the result by the [Old Price] as follows:
And there we have it, a table showing where better pricing is available in our benchmarks (all the negative percentages in column E).
In a negotiation with your potential supplier you could take just the products where the prices are better than the ones that they offered in our original table 1 and ask them to match the better price so that you have the lowest known price from the combination of the two price lists.
This technique can be used for comparing any two price lists. For example you might publish your own price list on a regular basis and need to use a vlookup to put the new prices into the existing price list.
Source: www.excelvlookuphelp.com
