Ask Your Question
0

Lookup by column and row values

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

sergs gravatar image

updated 2017-11-01 14:20:57 +0200

I have a MxN table with first column and row used as labels (see https://imgur.com/a/n68oM 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 retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

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

Question Tools

1 follower

Stats

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

Seen: 3,713 times

Last updated: Nov 01 '17