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 are the formulas ?



Put this formula:


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 Management