«

»

Vlookup to the left in Excel

Problem of 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. Like for instance in this document where the ID is the code country.

To return the population based on the country code, you simply use the VLOOKUP function as it is explain in this article

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.

How to return data on the left

So, because the function VLOOKUP is not build to return data on the left of the ID column, we can't use this function.

But if we use the functions INDEX and MATCH together, it's possible to build a formula to solve the problem.

Construction of the INDEX function

The INDEX function allows you to search for an item in a table. The function needs 3 parameters:

  • First parameter contains the table of search
  • Second parameter represents the line of search
  • Third parameter is not used in this case

So, we will start the formula by selecting only 1 column, the column containing the result to return.

=INDEX($A$2:$A$233,

Use the MATCH function

The second parameter of the INDEX function will be filled by 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

=MATCH($H$3,

And 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 😎😍👍

Include the MATCH inside the INDEX function

So, we just have to insert the result of the MATCH function in the second parameter 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))

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/vlookup-to-the-left-in-excel/


Leave a Reply

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