«

»

How to Lookup Values to left column?

The VLOOKUP function searches for data in a table by returning data always to the right of the column containing your ID. However, it is sometimes necessary to us to return the value that is located into the left columns of your ID. As the VLOOKUP function is not able to do, we can solve the problem by using the INDEX and MATCH functions.

Presentation of the problem

In the following example, you have a cell H2 menu that contains all the codes of the countries in the world. Based on this value, we want to recover the country’s population and the name of the country.


Change the value of the country code
in the worksheet below to see

  • the value of the population changing
  • the name of the country changing

Hey !!! It works 🙂 🙂 🙂 🙂

How to lookup to the left

To return the number of people of a selected country, simply use the VLOOKUP function as it is explain in this article. On the other hand, to return the name of a country linked to the code selected, the VLOOKUP function can not be used here. To work around the problem, we will used the functions INDEX and MATCH to solve the problem.

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

  • the first parameter contains the table of search
  • the second parameter represent the line of search
  • the third parameter represent the column of search

But in our case, But in our case, we can omit to use the setting of the column  🙂

Important Note: When you select your data range (first parameter of the INDEX function), do not include headers failing to introduce a lag in your search results

Construction of the INDEX function

To return the name of the country, we will start by writing the INDEX function and we will select the only one column, the names of the countries. The formula begins as follows:

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

Then, for the second parameter we will cleverly use the MATCH function. The MATCH function returns the position of an item in a list. We will write a formula that return the position of the country code selected in the column B . The formula is:

=MATCH($H$2;$B$2:$B$233;0)

For example, if we choose the code AL, the MATCH function returns the value  6 corresponding to the position of the code AL in the list of country codes (column B). The setting 0 means that we are looking for the exact value AL. The default setting of 1 means that we seek the approximate ratio which can lead to errors in the result of the formula.

Function_INDEX_MATCH_1


Now, we’ll just add this formula for the second parameter of the INDEX function in order to return the name of the country.

=INDEX($A$2:$A$233,MATCH($M$2;$B$2:$B$233,0))

The function can be interpreted as follows: We return the name of the country in function of the rank of the country code in the column B.

4 comments

Skip to comment form

  1. Max

    Does work just fine, the final formula simply contains a typo: the match part of the function refers to cell M2 instead of H2
    use this formula to make the above example work:
    =INDEX($A$2:$A$233,MATCH($H$2;$B$2:$B$233,0))

  2. Anonymous

    Doesn’t work

  3. Francisco

    Can you please make the exercise available for down loads, in order to exercise.

  4. Leon

    good job 🙂

Leave a Reply

%d bloggers like this: