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.

=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

pinpalchris wrote:
Hi,
I tried most of the posts for vlookup, but wasn't successful. Here's my
problem:

Best way is to concatenate both criteria in a temporary column and use
the normal vlookup function

ColumnA | ColumnB | ColumnC | ColumnD
Tokyo | Vancouver | TokyoVancouver | 200
Singapore | Vancouver | SingaporeVancouver | 100
Singapore | Seattle | SingaporeSeattle | 150

I have separated columns by " | " symbol

Now I have added a temporary column in column C.
Formula in C2 should be =CONCATENATE(A2,B2)
Formula in C3 should be =CONCATENATE(A3,B3)
Formula in C4 should be =CONCATENATE(A4,B4)

Now if A7 has "Singapore" and B7 has "Vancouver" and if you want to
find the rate in D7, then use this formula,

=VLOOKUP(CONCATENATE(A7,B7),C1:D4,2,FALSE)

Sandy

Hi Dave,

thanks, this worked perfectly! My problem's solved.

Have a good one,

Chris

"Dave Peterson" wrote:

=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

Thanks for your help Sandy!

Very much appreciated!

Have a nice day,

Chris

" wrote:


pinpalchris wrote:
Hi,
I tried most of the posts for vlookup, but wasn't successful. Here's my
problem:

Best way is to concatenate both criteria in a temporary column and use
the normal vlookup function

ColumnA | ColumnB | ColumnC | ColumnD
Tokyo | Vancouver | TokyoVancouver | 200
Singapore | Vancouver | SingaporeVancouver | 100
Singapore | Seattle | SingaporeSeattle | 150

I have separated columns by " | " symbol

Now I have added a temporary column in column C.
Formula in C2 should be =CONCATENATE(A2,B2)
Formula in C3 should be =CONCATENATE(A3,B3)
Formula in C4 should be =CONCATENATE(A4,B4)

Now if A7 has "Singapore" and B7 has "Vancouver" and if you want to
find the rate in D7, then use this formula,

=VLOOKUP(CONCATENATE(A7,B7),C1:D4,2,FALSE)

Sandy