use 2 fields in vlookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
Click to expand...

and this table below is in A1:D4 of sheet: Store
Store PO# SAS10 LDM03
Serial# Serial#
1 444 ? ?
2 555 ? ?
Click to expand...

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 ..
 
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)
 
Back
Top