How to VLOOKUP in excel – step by step VLOOKUP function

VLOOKUP function is a powerful tool for Microsoft Excel data management. VLOOKUP stands for Vertical Lookup as this function search for data vertically in a table. VLOOKUP supports approximate and exact matching for a vertical column of a table. Lookup data must appear in the first column of the table that is considered.

If I  simplify the above statement if you want to look for a value in the vertical direction of a table you can use the VLOOKUP function.

Arguments for the VLOOKUP function

The followings are the arguments for the VLOOKUP function:

• lookup_value – It means what you are looking for in a table. It is mandatory.
• table_array – From which table you will look for the value? It is also mandatory.
• col_index_num- From which column you will extract the value?
• [range_lookup]- TRUE/FALSE, TRUE/1-approximate match and FALSE/0- exact  match. It is optional.

You can simply understand if you think in this way, while typing =VLLOKUP( what to look for, where to look for,  column number, and approx or exact match)

How to VLOOKUP in excel

Let us prepare an Excel dataset for practicing the VLOOKUP function. I have prepared a dataset for you to explain a VLOOKUP function. Let’s have a look.

Table 01 says the Country’s Name and its Capital, Currency, and Population. What I want is to fill the population column of Table 02 using the VLOOKUP function of Excel. I have changed the sequence of countries in Table 02. let’s do the job.

For Norway, I want to write the formula using the VLOOKUP function. How it will look like?

Explanation of the argument:

• lookup_value – what you are looking for in a table. B13 as i am looking for Norway population.
• table_array – From which table you will look for the value? B2:E9 as Table-01 range of cells is B2:E9
• col_index_num- From which column you will extract the value? 4 if Norway is column 1 then population is at Number 4
• [range_lookup]- TRUE/FALSE, TRUE/1-approximate match and FALSE/0- exact  match. 0 as i want exact match.

Hope you understand the argument. I have discussed them one by one for your better understanding. Let’s check my example dataset.

Now, if drag your mouse to fill another cell, what will happen? Will it work? let’s check.

When you drag with the mouse pointer it does not work. But it worked for a few rows. Why? You have to understand the fact.

The fact is the range of cells is changed in every row. For Table 01, the range of cells was B2:E9 but it changed in every row. Such as for Qatar it changed to B3:E10, for Finlan it changed to B4:E11. So, VLOOKUP finds Qatar but cannot find Finland. I hope, you understand. That is why VLOOKUP finds Qatar but cannot find Finland. But is there any solution for this?

So that we can use the mouse cursor and drag the formula for other rows. Yes, of course. You need to be a little bit tricky. You need to fix the table range of cells. How to do that? Let’s check the below formula of the VLOOKUP function.

What additional I have done here, I have used the$ sign to fix the range of cells of Table-01. Now if I use the mouse pointer and drag all rows will show the exact data. Let’s check.

Remember, we should use the $ sign before and after the column number.

Now check the complete VLOOKUP at a glance.

What if Value Not Found

If lookup value not found in Table 01 then what result will return by VLOOKUP function? Do you know? Lets check it.

I typed country name “India” what happen the?

If lookup value not found then result comes with #NA. We can use IFNA function to avoid this error.

IFNA Function

You should write your message inside a double qoutation as i typed “Not found” after a comma. Bracket should be closed as well.

I hope, the content How to vlookup in excel – step by step VLOOKUP function is helpful for you. Thank you.