How to use Google Sheets Vlookup Formula

 

If you are a spreadsheet user and frequently use spreadsheets for MIS work of your office task or any data-related work you might be using vlookup function in Excel. Today I’ll show you how you can use vlookup in Google Sheets to do your task with ease and share completed tasks with your boss or colleagues easily.

Vlookup is a very powerful function built-in Google’s popular spreadsheet application Google Sheets. Vlookup or “vertical lookup” takes advantage of vertically aligned data ranges or tables to easily locate data associated with a given value.

Let’s take an example to understand why vlookup function you should learn;

Here two table of HR data is given: Table1 & Table2, you need to make a report by adding a new column called Employee ID with Table1 data. How to do that?  

Now, you are thinking that you can do this by simply copy the column “Employee ID” from Table 2 and paste it to the right of Table 1, that’s so easy.

Yes, you can do that for this tiny data, but if you have 1000 or 10000 or more employee data, and both tables not having the same set of data how can you do it immediately?  Is it that easy?

Again a big yes if you do it by using the VLOOKUP function.

Let’s do it step by step……

Step 1 :

Please make sure that the common data field in both tables is at the extreme left of the Table. Here in these two data tables, the common field is the NAME column and is at the extreme left.

Step 2:

For output report add a separate column at the extreme right of Table 1 with the same formatting and name it “Employee ID”.

Select cell E31 of the output table and type the VLOOKUP function in the cell.

Step 3:

In the VLOOKUP function four criteria need to provide, they are:

1. Search Key : 

It means which data you want to match to get the required data. In this example, we need to get the employee Id of MR.A.So search criteria are the cell number (A31) of MR.A.

2. Ranges:

 It means from which data range you want to get the required data. Here you need to get employee ID from Table 2 data, so select A10:D13 Table 2 except the heading of the table and fix the range using $ sign ($A$10:$D$13).

3. Index :

 It means In which of the Index/column the required data is present. Here” Employee ID“is column number 2.

4. is_Sorted (Optional Key) : 

And the optional key is TRUE or FALSE, by default it is True or if you are not mentioned in a formula it is assumed as true, but it is recommended to set it as False because it returns an exact match. Here I have used the False key for the exact match.

Step 4:

Drag the cursor from E31 to E34 to complete the task.

So by using the VLOOKUP function you can do it within 1 minute.

Sometimes it is possible that some data doesn’t match exactly as both tables have different sets of data, in that case, the VLOOKUP function gives  # N/A error, which means some data is not matched between both tables.

If you collaborate or share this output report with your boss it looks unprofessional due to #N/A error, Isn’t it?

So here is the solution that makes a perfect report for you.

Here you can use =IFNA () function along with Vlookup as the screenshot displayed below :

It gives you the output report like this :

Conclusion :

You have learned the basic Vlookup function and Ifna function to generate a professional report. And remember that the Vlookup function is one of the most powerful functions. If you combine another function with it, it is called advanced Vlookup formulas that can generate exceptional dynamic reports within a few minutes.

If you like this article helpful please make your comment and share it with your friends, also give your valuable suggestions for future articles.