Find the value on the right cell of the key-CodePudding

I have the following data table:

Is there any way to retrieve the value on the right of the "h" key (result would be 8) for example which is on the middle of the table ?

I tried VLOOKUP, HLOOKUP which seems to search on the first column / row and doesn't find it. LOOKUP alone is only searching on an unidimentional data table.

Thank you for your time

CodePudding user response：

If keys are unique you can use INDEX:

=INDEX(A1:F4, SUM(ROW(A1:F4)*(H2=A1:F4)), SUM(COLUMN(A1:F4)*(H2=A1:F4)) 1)

The formula also works in excel, just in pre-O365 versions it has to be entered as an array formula.

CodePudding user response：

try:

=IFNA(VLOOKUP("h"; {A:B;C:D;E:F}; 2; 0))