I have a MxN table with first column and row used as labels (see for screenshot:)
Is it possible to extract value from such table having row and column label? For example, for row labeled “5” and column labeled “3,00%” resulting value will be “5,30914”. I tried with VLOOKUP and HLOOKUP but these functions does not work for me, as they use column index, not value.
(edit: activated screenshot - AK)
Use MATCH to determine the row and column, then ADDRESS and INDIRECT to get the value of that cell.
For example, look up the cell where the value in column A is 8 and the value in row 1 is 4,00% (0,04).
=INDIRECT(ADDRESS(MATCH(8;A2:A11);MATCH(0,04;B1:G1)+1))
As a further improvement, instead of A2:A11 and B1:G1, named ranges may be used.
Useful answer, but please improve that by adding a zero to the each of the MATCH functions, otherwise the approximate lookup could give a nasty surprise.