The VLOOKUP function is one of the most powerful tools in Excel. We all know that Excel has plenty of functions and formulas to perform calculations on it. On that note, if you are a regular Excel user and manage a huge data, then you might want to look for specific data from your spreadsheet, at that time, you can make use of this VLOOKUP function. As Excel stores all of its values in rows and columns, we can get the particular value by using this feature.
Searching for a specific value from large data manually is much more difficult for everyone. In order to simplify this process, we can use this function. Using this feature, not only saves your time but also reduces errors by automating the operation. In this post, we will let you know what is VLOOKUP and how to use the VLOOKUP function in Excel with proper examples. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
What is VLOOKUP?
As we said earlier, VLOOKUP is a built-in function in Excel. It helps to look for a specific value or data in the spreadsheet. Instead of doing it manually, you can quickly extract the value based on your need from the range with the help of this function. In VLOOKUP, the letter “V” represents “Vertical“, so it is a vertical lookup. This function searches for a value vertically in columns of your selected range. Let’s see how to apply this function in the worksheet in the upcoming sections.
Syntax:
Following is the general syntax of the Excel VLOOKUP function.
- To use this in your worksheet, select a cell and enter the formula.
- Hit the Enter button to get the result.
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
Arguments:
- lookup_value – It is the value that you want to look for in the table.
- table_array – The input range where you want to look for a value or extract the value.
- column_index_num – It indicates the column number from where you want to extract the data.
- range_lookup – It is an optional one. This value can be a TRUE (approximate match) or FALSE (exact match).
Motive:
- In order to look up a value in a table and extract an approximate or exact match.
Return Value:
- It is the matched value from the table.
What Will VLOOKUP Function DO?
The VLOOKUP function will search for a value through a large amount of data in your Excel spreadsheet and extract it as a result. In Excel, this function is categorized as “Lookup and reference.” In order to use this function, we should have a worksheet with multiple entries of data. And it would find out the approximate or exact match from the columns in the specified range.
With the help of the VLOOKUP function, we can perform the following lookup operations on your spreadsheet. There are multiple ways available for using this single function.
| Lookup Method | Description |
|---|---|
| Find Exact Match | This method helps to find the exact match from the table. |
| Approximate Match | It will find out the approximate match from the table. |
| First Match Only | With this method, you can get the first match by ignoring the replicated values in the table. |
| Wildcard Match | It will find data based on the partial match on a lookup value. |
| Two-way lookup | This will find matches on both rows and columns with the help of the MATCH function. |
| VLOOKUP and #N/A Errors | In order to replace the #N/A error with another word, we can use the IFNA and VLOOKUP functions. |
| Multiple Criteria | You can find a match based on multiple criteria using & operator inside the VLOOKUP function. |
For instance, we have a list of persons with this office ID, name, gender, age, and Email address. From these data, we cannot have a proper order of ID numbers. Yes, they are random numbers inserted in the first column. Based on the specific ID number, we are going to extract an Email address of a person.
In order to get the exact match value based on the given ID number, we need to use the following formula.
=VLOOKUP(I2,B3:F11,5,FALSE)
In this formula, the lookup value is placed in cell I2, then we mentioned the input range. The number 5 indicated the fifth column in the table from where we want to retrieve the data. And FALSE is used to get the exact match from the table. If you want to get the approximate result, then you have to mention it as TRUE.
Now, click on the Enter button to execute it and get the result as shown in the below screenshot.
In the above image, we have highlighted the row, where you can find out the resulted email address “[email protected]” in column F. If you have thousands of records in your worksheet then you can easily find out a specific value by using this VLOOKUP function.
We hope that the given scenario helped you to understand the VLOOKUP function. If still you have any confusion, then move to the below section, where we will show you a simple example.
Alternate Ways to Use VLOOKUP Function
Let’s see a simple example to know how to use the VLOOKUP function in the spreadsheet.
Find Exact Match
- The following screenshot has shown the list of people’s names and their countries. Here we have used only two columns. And will need to retrieve the country name of a specific person based on his/her name.
- In order to get the result, we need to apply the following formula. Here we want to get the country name of the person “Catalina.”
=VLOOKUP(F2,B3:C11,2,FALSE)
- Once you have applied the formula, just click on the Enter button to print the result.
- The VLOOKUP function will search for the specific value in the range vertically and then find out the exact match for it.
- By doing so, you can quickly retrieve any data from the Excel table even though it is too large or small. Hopefully, this simple example helped you to know the purpose of this function clearly. Try using it on your worksheet and extract information ASAP.
Approximate Match
In some cases, we will not find out the exact match in the given range. At that time, it may be helpful to find the approximate match in your worksheet. For that, we can use the VLOOKUP function. Here we come up with a simple scenario to make it easy for you.
For example, we have a list of furniture items with their price tags and there are some offers available for each item. We want to find the approximate match for each item’s offer percentage.
In order to get the offer percentage, we need to apply the following formula.
=VLOOKUP(C3,$F$3:$G$6,2,TRUE)
After applying the formula, hit the Enter button to get the result. In order to fill down the remaining cells, you need to drag down the cells, as shown in the below screenshot.
First Match Only
Sometimes, there are a lot of duplicate values available in the spreadsheet. In order to ignore those replicated values from the selected range and find out the first match, we suggest you use the VLOOKUP function.
For example, we have listed a team’s colors and scores in every quiz competition. In the following image, you will see three teams which are names Red, Yellow, and Green. And we will get the first matching value of the team “Green.”
You need to apply the following formula in a cell where you want to get the result, as shown in the below image.
=VLOOKUP(E3,B3:C8,2,FALSE)
Now, hit the Enter button to get the result, as shown in the below screenshot.
Wildcard Match
The VLOOKUP function supports wildcards. With its help, we can find data based on the partial match on a lookup value. For example, we have come up with a list of person’s name and their details. And we will get one specific person’s details with the particle lookup value “Geo.”
In order to get the first name based on the partial lookup value, enter the following formula.
=VLOOKUP($H$2&”*”,$B$3:$E$7,2,FALSE)
After entering the formula, you need to hit the Enter button.
To fill down the remaining values, you need to change the column_index_num in the formula. So that you will get the corresponding results.
Last: =VLOOKUP($H$2&”*”,$B$3:$E$7,1,FALSE)
ID: =VLOOKUP($H$2&”*”,$B$3:$E$7,3,FALSE)
Group: =VLOOKUP($H$2&”*”,$B$3:$E$7,4,FALSE)
Two-way lookup
Inside the VLOOKUP function, the column_index_num is normally given as a static number. Instead of using it, we can use a MATCH function to find the needed column. This method is called a dynamic two-way lookup. It will find matches on both rows and columns.
For example, we have come up with the sales details of the organizations. Each person will sell products of their organization for a certain amount every month.
From the above list, we will need to get the sales details of the person Lilly in Feb month. In order to look up both values and get the correct match, use the following formula.
=VLOOKUP(H2,B3:E9,MATCH(H3,B2:E2,0),0)
Once you have entered the formula, you will need to hit the Enter button to get the result.
VLOOKUP and #N/A Errors
Sometimes, the VLOOKUP function returns a #N/A error. But we can replace it with another word using the IFNA and VLOOKUP functions.
For instance, we have come with the employee IDs with their names and salary details. From that list, we want to get a particular employee’s salary based on the given ID. As per the analysis, we need to use a formula or value inside the IFNA function. In order to search for a specific value in the range, we suggest you use the VLOOKUP formula. To be more precise, the VLOOKUP function will look for the specific value in the range based on given conditions and returns the result. You can find the syntax and explanation of the VLOOKUP function at the end of this section
Now, we will get the salary of the person from the range based on ID 11. If the IFNA function doesn’t find the match, then we will need to print the value “not found.” For that, we need to apply the following formula.
=IFNA(VLOOKUP(G2,B3:D10,3,FALSE),”not found”)
Now, click on the Enter button to execute it and get the result. In the below image, we will get the output value as “Not found“, because there is no ID called 11 in the specified range.
Find a Match Based on Multiple criteria
In general, the VLOOKUP function doesn’t handle multiple criteria. But there is a way to find a match based on multiple criteria. For that, we need to join multiple fields together and use them with the VLOOKUP function. In order to join the fields, we can use the “&” (AND) operator.
- For example, here we come up with the set of student names and their groups. We want to extract a group of the specific student based on the given first and last names.
- In order to extract the result from the given table, enter the following formula.
=VLOOKUP(H2&H3,B3:E7,4,0)
- After entering the formula, hit the Enter button to get the result.
Thus, we have seen various ways to use the VLOOKUP function in the Excel spreadsheet based on our needs. So that we can quickly extract the matching value from the large data table.
Citations
We have referred to the below-mentioned articles to fully understand the purpose of the Excel VLOOKUP function. They helped us a lot. You can also check out them using the given links: VLOOKUP function[1], What is LOOKUP?[2].
- https://www.geeksforgeeks.org/vlookup-function-in-excel-with-examples/
- https://en.wikipedia.org/wiki/Lookup_table
Summary
So far, we have learned the ways to use the Excel VLOOKUP function in the spreadsheet. The given examples help you understand the purpose of this versatile tool of Microsoft Excel. Once you have used to it, you can finish your task more efficiently. It makes you easily work on the complex data stored in your workbook and find the specific one as quickly as possible.
If you found this post helpful to you, kindly share comments in the below section. Thanks for visiting AawExcel. Keep Learning!!
If you want to know what people think about this VLOOKUP Function of Excel, then we suggest you look for it on the Quora interaction webpage. From where you can get to know more regarding the Excel VLOOKUP function. Here, we have provided the link to Quora’s official website.
Video Tutorial
The following video will show you how to use the Excel VLOOKUP function in the spreadsheet with proper examples.
Read Ahead:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Hi there, I’m Sridhar – an Excel enthusiast with over 10 years of experience working with software. I’m passionate about using Excel to solve complex problems and streamline business processes. Over the years, I have helped businesses of all sizes to improve their operations and save time and money.
Aside from working with Excel, I also enjoy writing and sharing my knowledge with others. You’ll often find me contributing to the AAW Excel blog, where I provide tips, tricks, and tutorials that are easy to understand for readers of all skill levels.
