HLookup ? or use Vlookup?

[Kenji]

Dear Admin,

I got one question, I wish to lookup some value but I can't , hope to get your advice!

Example:
1 2 3 4 5 row 1
2 1 4 5 6 row 2
a b c d e row 3

I wish to lookup row 1 and row 2 and return row 3 value, any suggestion on this ??

Sample answer : 1 2 = a
2 1 = b


Thanks and Regards

[NBVC]

Try:

=INDEX($A$3:$E$3,MATCH(1,INDEX(($A$1:$E$1=X1)*($A$2:$E$2=X2),0),0))

where A1:E3 contains your whole table and X1 and X2 contain your input variables, respectively for rows 1 and 2...

[Kenji]

Dear NBVC,

Can You explain abit about how to use this formula ?
MATCH(1,INDEX(($A$1:$E$1=X1)*($A$2:$E$2=X2),0),0)
I do not know why
MATCH(1,
this part.
and
,0),0)

Can you further explain abit ? Others I still can guess what it is from the internet , Please advice thanks.

[NBVC]

This part: ($A$1:$E$1=X1)*($A$2:$E$2=X2)

returns separate arrays of TRUEs and FALSEs based on conditions being satisfied...

When Multiplied together the results are 1s and 0s... for example TRUE*TRUE = 1, TRUE*FALSE=0, FALSE*TRUE=0 and FALSE*FALSE=0

The INDEX(($A$1:$E$1=X1)*($A$2:$E$2=X2),0) creates a 1-dimensional ARRAY of 1's and 0's based on those results.

Then

MATCH(1,INDEX(($A$1:$E$1=X1)*($A$2:$E$2=X2),0),0)

looks for the first 1 in that Array (hopefully the only 1, if you truly have uniques datasets)... and returns the position so that INDEX($A$3:$E$3,MATCH(....)) indexes that position and returns the corresponding item in the indexed range.