match A1&B1

[spence]

i have two ranges, A1:A7 and B2:B7. and on another sheet
the similar, only it is K1:K7 and L1:L7. i need a
formula to lookup or match A1&B1 in the two ranges. is
this possible.

A B
1 One A
2 Two A
3 Three B
4 Four A
5 Four B
6 Five C
7 Six A

basically i wil have two cells concatenated together and
i want to look up the concatenated value in 2 ranges with
their values separated. so if cell C1=A1&B1, i want it
to look up that value and return what is in K1&L1. hope
that isnt too confusing. TIA

 

[Bernie Deitrick]

Spence,

You would need to use a helper column, with this formula in cell C1
=A1&B1
copied down to C2:C7. Then put what you want to find in D1.

To return data from the next sheet, use the formulas

=OFFSET(Sheet2!K1,MATCH(D1,C1:C7,FALSE)-1,0)
=OFFSET(Sheet2!L1,MATCH(D1,C1:C7,FALSE)-1,0)

HTH,
Bernie
Excel MVP