A drawback of the VLOOKUP function is that it can only look up values in the leftmost column of a table. However, sometimes you need to look up a value in any column and return the corresponding value to the left. To achieve this, simply use the INDEX and the MATCH function.
1. The MATCH function returns the position of a value in a given range.
Match Function in Excel
Explanation: 104 found at position 4 in the range $G$4:$G$7.
2. Use this result and the INDEX function to return the 4th value in the range $E$4:$E$7.
Index Function in Excel
3. Drag the formula in cell B2 down to cell B11.
Drag the Formula Down
Note: when we drag this formula down, the absolute references ($E$4:$E$7 and $G$4:$G$7) stay the same, while the relative reference (A2) changes to A3, A4, A5, etc.
Did you find this information helpful? Show your appreciation.

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views