Left Lookup in Excel

by

The VLOOKUP function only looks to the right. To look up a value in any column and return the corresponding value to the left, simply use the INDEX and the MATCH function.

1. The MATCH function returns the position of a value in a given range.

Worked Example:   Map inputs to arbitrary values 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.

Worked Example:   Basic INDEX MATCH approximate in Excel

3. Drag the formula in cell B2 down to cell B11.

Worked Example:   Example of COUNTIFS with variable table column in Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *