Comparing Two Tables

I think what you want is VLOOKUP. Attached is an example. In file SKU1, the prices exist, in SKU2, they are "looked up." The IF statement checks first to see if there is a match (SKU vs SKU) and if not, it returns "No Price" If there is a match, it does the vlookup.

ChemistB

My SKU1.xls file is an edited file that contains only the wine entries for our store. My SKU2.xls file is a pricing file that contains prices for every item in the entire store, so SKU1 has 4300 rows and SKU2 has 10,000. Would this still work?

Yes, for example in the price column of SKU1 (assuming starting in row 2)

Please Login or Register  to view this content.

and drag down for 4300 rows.

ChemistB

I tried that and it gave me a No Value designation. Attached are cut versions of my SKU1 and SKU2 files. Perhaps I'm putting it in wrong?

Of course since SKU2 is edited you won't be able to get an answer out of it, but you'll be able to see what format my files are in.

You need to have the price sheet sorted, vlookup should then work in sheet1

If you're using the FALSE argument as your fourth arg in the VLOOKUP, it shouldn't need to be sorted since each SKU is unique. I'll look at this today and see if I can find out why it's not working.

ChemistB

None of the SKU's in Sku1 are in Sku2. Is that simply because you removed a bunch or is there something I'm missing?

ChemistB

"by default a table must be sorted in ascending order" its in the help for vlookup

From the Excel help

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

Try it.

ChemistB

http://xantar.cookiethievery.com/SKU1.xls

http://xantar.cookiethievery.com/SKU2.xls

SKU1 contains SKU numbers and names of products. SKU2 contains SKU numbers and prices.

These are the REAL files, so let me know if you guys can figure this out. Nothing works for me (mostly because I'm doing it wrong!)

You need to use an index and match, try this formula in column C of SKU1
=INDEX([SKU2.xls]prc.xls!$A:$C,MATCH(A2,[SKU2.xls]prc.xls!$A:$A,0),3)

use your data as I had to cut down for size to post