“Left” search in Excel

Feature lack VLOOKUP (VLOOKUP) in that it can only look for values ​​in the leftmost column of the table. However, sometimes you need to look up values ​​in any column and return the corresponding value from the column to the left. To achieve this, just use the functions INDEX (INDEX) and MATCH (MORE EXPOSED).

1. Function MATCH (MATCH) returns the position of a value within a given range.

   =MATCH(A2,$G$4:$G$7,0)

   =ПОИСКПОЗ(A2;$G$4:$G$7;0)

   Explanation: The number 104 is found in row 4 of the range $G$4:$G$7.

2. Use the result, and the function INDEX (INDEX) will return the fourth value from the range $ E $ 4: $ E $ 7.

   =INDEX($E$4:$E$7,MATCH(A2,$G$4:$G$7,0))

   =ИНДЕКС($E$4:$E$7;ПОИСКПОЗ(A2;$G$4:$G$7;0))

   

3. Drag formula from cell B2 to the cell B11.

Note: When dragging the formula down, the absolute references ($E$4:$E$7 and $G$4:$G$7) will remain the same, while the relative reference (A2) will change to A3, A4, A5, and so on.