How to use a VLOOKUP formula to do price benchmarking

By | December 8, 2015

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


benchmark table 1

 

Table 2

price benchmarking 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:

price vlookup benchmarking difference

 

 

And there we have it, a table showing where better pricing is available in our benchmarks (all the negative percentages in column E).

price benchmarking with vlookup - result

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.

 

 

 

 

 

 

 

 

 

 

Want to get good at using the VLOOKUP function?

Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a VLOOKUP pro.

Leave a Reply

Your email address will not be published. Required fields are marked *