VLookUp with 2 cell criteria

**matt905** · 07-30-2009, 11:25 AM

As the title says what I need to do is match a part number (col A) and a purchase order (col B) on sheet 1 to sheet 2 and return a delivery date from sheet 2

I know that a vlookup will only look at one cell as it's "lookup_value" and unfortunatley part numbers have multiple PO's and PO's have multiple part numbers.

Any ideas?

**Miraun** · 07-30-2009, 11:29 AM

What about creating a concatenated column on both pages? The page where you're doing the vlookup, and the one that you're referencing?

Just insert column C, use
=Concatenate(A2,B2)
Into C2, and fill down

Then just use the Vlookup based on Column C...

Could always hide row C afterwards, or throw it off to the end of the table, row IV if you want.

**ChemistB** · 07-30-2009, 11:34 AM

You'll need to insert a column before your Part Number on Sheet 2 and concatenate your part # and Pur Order in those cells. You can hide the column when you are done.

Please Login or Register  to view this content.

Then your lookup on Sheet1 (assuming delivery date is in column D)

Please Login or Register  to view this content.

Does that work for you?

**JBeaucaire** · 07-30-2009, 11:42 AM

A sample sheet makes this much easier to demonstrate, but you can try this with no helper column and see if it works for you. Switching from VLOOKUP to INDEX/MATCH makes this easier and more robust:

=INDEX(Sheet2!C$1:$C$100, MATCH(A2 & "-" & B2, INDEX(Sheet2!$A$1:$A$100 & "-" & Sheet2!$B$1:$B$100, 0), 0))

Translates to:
=INDEX(RangeofDates, MATCH(Prt# & "-" & PO#, INDEX(RangofPrt#s & "-" & RangeofPO#s, 0), 0))