How to perform research to the left with VLOOKUP. In fact it's not possible but you have 2 options
- Build a formula with INDEX or MATCH
This is the oldest technique, but the writing of the formula is not easy
- Use the XLOOKUP function
XLOOKUP doesn't matter where is the lookup column. It's the easiest way to perform research to the left.
Problem with the VLOOKUP function
The normal behavior of the VLOOKUP function is to return data from a table. But you can only return data on the right of the column containing the ID.
However, in some cases, you can not change the order of the columns. For instance in this document the ID is the code country.
To return the population based on the country code, you simply use the VLOOKUP function. But if we want to return the name of the country, which is on the left of the ID, the VLOOKUP function is not the way to solve the problem.
Method 1: The XLOOKUP function does the job easily
If you work with Excel Online or Excel 365, the easiest way to return a column on the left of the column of research is to use the XLOOKUP function.
- Select the value to research
- Select the column of research (only one column to select)
- Select the column to return whatever if it's on the right or the left of the research's column
=XLOOKUP(G3;tblCountry[ISO 2];tblCountry[COUNTRY])
Method 2: Solution with INDEX and MATCH
If you don't work with Excel 365, there is another technique to return data on the left. The technique is to use the functions INDEX and MATCH together.
- The INDEX function searches for an item in a table in the function of a position (position 1, 2, 3, ...).
- The MATCH function returns the position of a value in a list.
- Then, with this position in the range, INDEX will return the value
Step 1: The position with the MATCH function
The MATCH function returns the position of an item in a list. But let's write this function in a new cell to understand what this function will return.
- The first parameter of the function MATCH is the value we search (the ISO code)
=MATCH($H$3,
- The second parameter is the column where we look for this value
=MATCH($H$3,$B$2:$B$233,
- We finish the function with the value 0 or FALSE to indicate that we perform an exact research. It's exactly like the last parameter of the function VLOOKUP.
=MATCH($H$2,$B$2:$B$233,0)
Look at the result of this function for different values of countries. In each case, the function MATCH returns the position in the column B 😎😍👍
Step 2: Include the MATCH inside the INDEX function
Now, we have to insert the result of the MATCH function as the second argument of the INDEX function.
Like that, we are able to return a value that is on the left of the ID 👍😍😎
=INDEX($A$2:$A$233,MATCH($H$3;$B$2:$B$233,0))
