vlookup with 2nd column criteria match

**daniel-g** · 05-15-2013, 02:39 AM

Good Afternoon,

Struggling with the corner way, or anyway for that matter, of implementing the below situation.

On one tab I have invoiced data in a table "SalesData" with a couple columns for customer, sku, date, rev, etc. On a second tab I have information relating to the skus (table "SKU_INFO") with classification information, but the SKU is repeated for each type of classification, ie

SKU Classification Attribute
123 Size large
123 color blue
123 style 454624
125 Size small
125 color yellow
125 style 333424

On the salesdata table I want to add a vlookup/if (?) combo that will lookup the color only for the sold sku, so I would need a vlookup on sku where classification equals "color". Is this possible?

Thanks

**FDibbins** · 05-15-2013, 02:47 AM

Hi and welcome to the forum

Hard to say without seeing some sample data, but my (blind) suggestion would be to add a helper column on sheet1 (?) to combine the SKU and "color", then do the vlookup based on that? If the helper is to the right of the rest of the data, you will need to use index/match, not vlookup

**Fotis1991** · 05-15-2013, 02:47 AM

Looks to me that YES. Is possible. Pls upload a small sample workbook

**FDibbins** · 05-15-2013, 02:49 AM

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**daniel-g** · 05-15-2013, 04:08 AM

Thanks for the welcome and quick replies.

FDibbins - I like the idea of joining SKU with attribute color and then doing a vlookup based on that value. I have that as
=VLOOKUP([@SKU]&"Color",SKU_INFO[[Column2]:[Column1]],2,FALSE) and it works fine. Thanks. Is this the quickest and more efficient formula as my SKU list will be approx 60,000 lines and the sales data approx 120,000.

Sample data attached. sample.xlsx