Ask Your Question

Lookup by column and row values

asked 2017-11-01 14:20:20 +0200

sergs gravatar image

updated 2020-10-19 13:23:48 +0200

Alex Kemp gravatar image

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)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-11-01 15:33:13 +0200

Jim K gravatar image

updated 2017-11-01 15:36:11 +0200

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).


As a further improvement, instead of A2:A11 and B1:G1, named ranges may be used.

edit flag offensive delete link more


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.

w2016 gravatar imagew2016 ( 2018-08-07 15:33:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-01 14:20:20 +0200

Seen: 8,489 times

Last updated: Oct 19 '20