VLOOKUP - Double criteria

[Rashid]

Hi to All,

Hi,

I want to do a double vlookup, where I want to bring back
cost_of_sales to sheet 1, if the location and item# from sheet 2
matches with the location and item# from sheet1.

The layout is as follows:

sheet 1
location item_# average_inventory

Sheet 2
location item_# cost_of_sales

Can any one help - please.....

Thanks,

Rashid

 

[Dave Peterson]

One way:

=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A1)*(Sheet2!B1:B100=B1),0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range (I used 100 rows) to match your data--but don't use the whole
column.

 

[Wild Jim]

Assuming that the data formats are both the same in sheets one and two, the
easiest way would be to concatenate the location and item numbers in both
sheets then just do a single lookup.