Combination of INDEX & MATCH Function can relieve you to get the result from left-most column of lookup column. VLOOKUP cannot directly retrieve values from columns to the left of the lookup column. In Excel, VLOOKUP only searches for values in the leftmost column of the table and retrieves corresponding values from the right columns.
The two functions used in Combination of INDEX & MATCH is :
- The INDEX function in Excel – To retrieve a value or reference to a cell within a range of cells, based on its row and column number. It usually uses other functions, such as MATCH, to perform more complex lookups and calculations.The basic syntax of the INDEX function is as follows:=INDEX(array, row_num, [column_num])
- The MATCH function in Excel – To find the relative position of a specified value within a range of cells. It returns the position of the matching value as a numeric value. It, then, uses in conjunction with other functions like INDEX and VLOOKUP. The basic syntax of the MATCH function is as follows:=MATCH(lookup_value, lookup_array, [match_type])
To retrieve values from columns to the left of the lookup column, you can use a combination of both.
=INDEX ( [columns to be retrieved] , MATCH ( [lookup value] , [lookup column] , 0 ), to be retrieved column)
Here’s an example to retrieve the score and grade of a specific student when the name is in column D, and the score and grade are in columns B and C, respectively:
For score, you can use the following formula:
=INDEX($A$1:$B$4,MATCH(C7,$C$1:$C$4,0),2)
For Grade, you can use the following formula:
=INDEX($A$1:$B$4,MATCH(C7,$C$1:$C$4,0),1)
If you really want me to explain this topic in more detail than please feel free to comment me in the below provided comment box. I will more than happy to help you.
