VLOOKUPs - combining?

[Jason O]

Hi

Any help on something would be greatly appreciated. Let's say I have 2
sheets with the same 3 columns:

Item | Year | Price

There are many instances of the same items in both sheets. The objective is
to take the price of an item from sheet 1 and add it to the 4th column of
sheet 2 in the row of the same item. So it has simply been a case of
vlooking up the item in sheet 1's range and returning it.

However, this is only OK when there aren't several records with the same
Item & different years. If there have been, I have been sorting by year and
doing a separate vlookup for each year range. Not particularly efficient.

Is there any way of combining the 'Item' VLOOKUP with a 'Year' VLOOKUP so it
checks that Item *and* year are the same on both sheets before returning
sheet 1's price to sheet 2? (And then even another one to match on an
additional 'Month' column?)

Any suggestions warmly received.

Cheers,

Jay
__

 

[Bob Greenblatt]

Hi

Any help on something would be greatly appreciated. Let's say I have 2
sheets with the same 3 columns:

Item | Year | Price

There are many instances of the same items in both sheets. The objective is
to take the price of an item from sheet 1 and add it to the 4th column of
sheet 2 in the row of the same item. So it has simply been a case of
vlooking up the item in sheet 1's range and returning it.

However, this is only OK when there aren't several records with the same
Item & different years. If there have been, I have been sorting by year and
doing a separate vlookup for each year range. Not particularly efficient.

Is there any way of combining the 'Item' VLOOKUP with a 'Year' VLOOKUP so it
checks that Item *and* year are the same on both sheets before returning
sheet 1's price to sheet 2? (And then even another one to match on an
additional 'Month' column?)

Any suggestions warmly received.

Cheers,

Jay
__

Te best way to do this is to have another column that is the Vlookup key and
use a formula to concatenate item, year, and month.

 

[Jason O]

Te best way to do this is to have another column that is the Vlookup key and
use a formula to concatenate item, year, and month.

Cheers Bob - that's an ideal solution, and a bit embarrassing that I hadn't
thought of it. It seems so obvious now

Thanks again,

Jay
­­

 

[Dave Peterson]

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

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 to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))