Excel VLOOKUP on multiple columns

1
votes

I have one excel sheet - "Records_Table.xlsx" with the following data -

enter image description here

In my another excel sheet - "User_Input_Table.xlsx" ,

enter image description here

the user will input the Product and state, and the catalog number will be generated from the "Records_Table.xlsx"

I am using vlookup formula - VLOOKUP(A2,B2,[Records_Table.xlsx]records_sheet!$A$2:$B$50,3)

but it is not showing any result, what am I doing wrong ?

2
excelvlookup

2 Answers

1
votes

use INDEX("pull from this col",MATCH("matching this","from this col"))

index/match is almost always better than vlookups

0
votes

You can also create a 'index' column (in both sheets) that concatenates multiple columns into 1 lookup column: 

=VLOOKUP(CONCATENATE(A2,B2),[Records_Table.xls]Sheet1!$C:$D,2,FALSE)

where $C is the new index column i.e. C2 = CONCATENATE(A2,B2) in User_input, and your lookup data starting from $D

Manual Indexed VLookup sample

Might be a hack, but I find it useful when doing a quick lookup on number of keys