=index(othersheet!$f$1:$f$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!$f$1:$f$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

pinpalchris wrote:

Hi,
I tried most of the posts for vlookup, but wasn't successful. Here's my
problem:
I have 2 values in the same row in table2 (cells A5 and B5), for which I'd
like to look up the matching row in table 1, which has a different structure.
The formula should return the value of column 6 of the matching row in
table1. Example:

table2:
A=Origin B=Destination C=rate
Singapore Vancouver (lookup result)

table1:
A=Origin B=Destination F=rate
Tokyo Vancouver $200
Singapore Vancouver $100
Singapore Seattle $150

Does anybody have a suggestion? Thanks.

--

Dave Peterson