Sunday, October 20, 2019

How to use VLOOKUP function in Excel

Well, in MS Excel VLOOKUP is a built-in function which is categorized as a Lookup/Reference Function.

According to techonthenet, The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.

Basically, this function is used for a large set of data in a single or multiple table to find or lookup a specific value in a faster way. But in this demonstration, we will use a single table contains small amount of data and find out some data through VLOOKUP function.

Basic Syntax of a VLOOKUP function:

= VLOOKUP (Lookup_value, Table_array, Col_index_no, Range_lookup);

It is clearly seen that, VLOOKUP function contains some arguments, so let’s find out what these arguments mean in the function.

Lookup_value: this is the value you are actually looking for in the first column of a table.

Table_array: it means the whole table or the range you would like to define from the table to lookup the value.

Col_index_no: the exact column in the table you would like to retrieve a value.

Range_lookup: this argument contains a logical value. If, True that means the approximate match of that value and if, False which means it will look for the exact match of the value from the table. It is an optional field and by default, it is True in nature.

Below screenshot will give you a clear understanding about the arguments passed in VLOOKUP function.

Now, in this state, we will create a new table from the left table exactly like the above one using a VLOOKUP function. So, let’s start, suppose we have this below table which contains small range of data for a small company. The table has four columns ID, NAME, AREA and SALARY and these columns have some values the same as in the table.

So, now we want to create a new table to lookup a specific one like you can say, we want to look up all the information of ID = 216 and ID = 197. To do that, first create a new table just right of the existing table using the same column name.

Then, write down the ID from the left table like 216 or 197 to your new generated table.

Now, it is time to use the VLOOKUP function to look up the data from the left table of that ID. Here, how you are going to create your VLOOKUP function for your first ID which is here 216. There are actually several ways to use VLOOKUP function in Excel but here I am showing you the easiest way to do that.

First, select the blank field below your column name. Here, for your NAME column see the below image for your clarification.

Then, just click ok button and this box will pop up.

Here, you can see the arguments list we already discussed before. So, it’s time to specify those arguments of the VLOOKUP function.

For our table and case, it will look like this,

Check instructions written for all arguments fields carefully and then proceed.

Then just hit the OK button and see the result you get from the VLOOKUP function.

You are all set to learn how to use VLOOKUP function already. 

Now for other columns in our table like AREA and SALARY you don’t need to write again & again the whole function. Just drag your mouse from left to right and see the magic. After doing that, you will get the final result like this,

Well, that’s enough for today! Hope you get some basic knowledge about VLOOKUP function in Excel and can perform some simple operation using this function.

If you are not sure about VLOOKUP function or have confusion about it or want to know more about VLOOKUP function and other Excel related topic then stay with tutorialabc and you can send your message via our Contact page and we will surely reply you back with your enquiry.

Thanks for your support and stay tuned for some new tutorial.

No comments:

Post a Comment