Comparing data

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
I have two sheets with data, for example product name, quantities sold and price. All product names on sheet 1 may not be there on sheet 2. So what i need to compare first is the product name and then the other data such as quantities and price.

Through vlookup formula i have figured out the common product names now how do i compare the other two columns as the data is really vast.

Thanking you in advance

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
Hi Surabhee,

This example from 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:
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.