use 2 fields in vlookup

[Guest]

I have a datarange that looks something like this:

PO# Model# Serial#
444 SAS10 R27
444 LDM03 554
555 SAS10 R28
555 LDM03 556

I have a Store worksheet that I need to update that looks something like
this (the ? is the field I am trying to update:

A B C D
Store PO# SAS10 LDM03
Serial# Serial#
1 444 ? ?
2 555 ? ?

Each store is directly related to the datarange row by PO#. However, I need
to also match the Model#s in order to fill the Store worksheet question
marks. I know column C on the Store Worksheet will alway contain model#s
that include "SAS10". I would like my vlookup, or your suggested formula to
check 2 cells( Match both the PO# and the Model# from the data range). Is
there a way to do this?

 

[Max]

One way

Assuming the ref table is in Sheet1, A1:C5, viz.:

PO# Model# Serial#
444 SAS10 R27
444 LDM03 554
555 SAS10 R28
555 LDM03 556

and this table below is in A14 of sheet: Store

Store PO# SAS10 LDM03
Serial# Serial#
1 444 ? ?
2 555 ? ?

Put in C3 (paste into the formula bar):

=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$5=$B3)*(TRIM(Sheet1!$B$2:$B$5)=C$1),0)),"",
INDEX(Sheet1!$A$2:$C$5,MATCH(1,(Sheet1!$A$2:$A$5=$B3)*(TRIM(Sheet1!$B$2:$B$5
)=C$1),0),3)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

Copy C3 across to D3, fill down to D4

This'll return all the desired values from the ref table in Sheet1
Unmatched cases will return blanks ""

Adjust to suit ..

 

[Myrna Larson]

For a simpler, non-array formula, you could add another column to the table,
between the model # and SErial # columns. Assuming the table is now in A1:C??,
Insert a new column C. In C2, put the formula =A2&" "&B2 and copy down. You
can hide this column if necessary, but be sure to copy the formula down if you
add rows to the table.

Then your formula looks like this: =VLOOKUP(<po>&" "&<model=>,$C$2:$D100,2,0)