Show the corresponding value when matching cells in horizontal and in vertical with a formula in an excel report

When I have a big table with a lot of data, it is not easy to find the correct value. I can do a vlookup but in such cases, it is quite limited, particularly when I have to match data not only in column but also in row before to display the good result.

formula excel

 

When I use the formula ?

When there is a table with many data and I need to match value in column and in row.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=INDEX()

=MATCH()

Put this formula:

=INDEX(B1:I10,MATCH("chocolate",A:A,0),MATCH("honey",B2:I2,0))

So I am asking to look in the column A to find “chocolate” then to search in the row 2 (B2:I2) “honey”. Once it finds it, it display the corresponding value inside the whole table (B1:I10)

Interesting Topics