Anyone who has ever worked with the VLOOKUP (vertical lookup) function in Excel will probably know that it only finds values to the right of the search column. Suppose an Excel sheet contains the following data:
If, for example, someone's account number needs to be looked up, this is fine with the VLOOKUP function. For example, if you enter a name in cell B10 in the example above, the account number will automatically appear in cell C10 if you enter the following formula in cell C10:
=VLOOKUP(B10,A2:C7,3,FALSE)This function works as follows: the entered search value in cell B10 is looked up in the first column of the matrix A2:C7, after which the value from the 3rd column (is the account number) of this matrix is displayed. Approximate match is set as FALSE, which means that the function returns a result only if the value found in the first column of the array is exactly equal to the search value.
But what if you want to enter the account number in the overview above and search for the corresponding name.This cannot be done with VLOOKUP because the value found is to the left of the search column. It is possible to use an helper column, but the use of an helper column should be avoided as much as possible. An alternative is to enter the following formula in cell C10:
=INDEX(A2:A7,MATCH(B10,C2:C7.0))This function ensures that with MATCH the position of the exact search value in the search column (C2:C7) is found, after which the value of the same position from column A2: A7 is displayed using the INDEX function.. In this way it is therefore possible to search vertically to the left of the search column.
With a modified formula, it is even possible to use VLOOKUP to find a value the left side:
=VLOOKUP(B10,CHOOSE({1\2},C2:C7,A2:A7),2,FALSE)This function is less simple than the version with INDEX + MATCH and is therefore not used very often.
Excel Office 365
In Excel for Office 365, the formula XLOOKUP can also be used for this. Availability of this feature depends on the type of subscription. The formula then becomes:
=XLOOKUP(B10,C2:C7,A2:A7)Questions / suggestions
Hopefully this article has helped you understand and apply the left vertical lookup. If you have any questions about this topic or suggestions for improvement, please post a comment below.
