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:
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:
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.
Now, we’ll just add this formula for the second parameter of the INDEX function in order to return the name of the country.
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.