Ask Your Question
0

Lookup by column and row values

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

sergs gravatar image

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

Alex Kemp gravatar image

I have a MxN table with first column and row used as labels (see for screenshot:)
https://imgur.com/a/n68oM

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
0

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

Jim K gravatar image

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

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.

edit flag offensive delete link more

Comments

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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 7,248 times

Last updated: Oct 19