Microsoft Excel is a deceptively powerful tool for data management. It helps users analyze and interpret data easily. Often not appreciated for the range of tasks it lets the user perform, Microsoft Excel is undoubtedly a powerful and very popular tool used by almost every organization, even today.
Excel provides an extensive range of functions that makes it easier to work with data. VLOOKUP in Excel is one such function. VLOOKUP works as a search function by looking for specific data vertically across a table or spreadsheet.
Let’s go ahead and understand what exactly VLOOKUP in Excel is.
What is VLOOKUP in Excel?
VLOOKUP stands for Vertical Lookup. As the name specifies, VLOOKUP is a built-in Excel function that helps you look for a specified value by searching for it vertically across the sheet. VLOOKUP in Excel may sound complicated, but you will find out that it is a very easy and useful tool once you try it. Look at the example below to understand VLOOKUP.
The VLOOKUP formula below looks for a Company name with Company ID 3.
In the next section, you will understand how to use the VLOOKUP function.
VLOOKUP Formula
We can use the VLOOKUP function with the help of a simple syntax. The Syntax for VLOOKUP is:
|
VLOOKUP(lookup_value, table_array, col_index_number,[range_lookup]) |
Where,
- lookup_value: This specifies the value that you want to look up in our data.
- table_array: This is the location where the values are present in excel.
- col_index_number: This specifies the column number from where we need to return the value.
- range_lookup: This has two options; if the value is set to FALSE, that means we are looking for an exact match. If the value is TRUE, then we are looking for an approximate match.
How To Find an Exact Match Using VLOOKUP?
VLOOKUP makes it effortless to look for an exact match from the table. Let’s take a look at how to do this with the help of an example:
- In the example below, we are using the VLOOKUP function to find the value of the exact match of ID from the given table. So, we set the first parameter as the lookup value, which is the cell H5.
- We specify the location of the table in the second argument. As you can see, the table location is A2:F11.
- The third argument specifies the Column Index number. This tells us what value should be returned from the row that we are looking up for. In the example, the product column is 3.
- The last argument is a Boolean Expression. Here, the value is set to FALSE for the VLOOKUP function to return an exact match for the value. An N/A error is displayed in case the exact value is not found.
With the help of VLOOKUP in Excel, we can look for an approximate match as well. You will learn this in the next section.
Become a Creator of Products that Delight Users
Free Webinar | 29 March, Wednesday | 8 PM ISTREGISTER NOW
How To Find Approximate Match Using VLOOKUP?
Approximate Match works by finding the next largest value that is lesser than the lookup value, which we specify.
In the example below, we use the VLOOKUP function to find out how much RAM specification a laptop priced 1300 EUR has. Also, we know this value is not present in the table. So, let's use the Approximate Match to find the solution. We need to sort the first column in ascending order. If not, VLOOKUP will return incorrect values.
- First, copy the price and RAM details to a new location, and specify your lookup value. Here, the lookup value is $1300.
Next, select your data range and click on the filter option to sort the values of the first column based on ascending order. After you click on the filter option, you will see the filter buttons enabled on your column headers.
- Filter the price details in ascending order. Click on OK.
- Now, enter your VLOOKUP formula. The first argument will be the VLOOKUP value. The second argument specifies the table range. In the third argument, we give the column number, so that values for that column are returned. Finally, the last argument is set to TRUE. This will allow the VLOOKUP function to find an approximate match for the value.
- After entering the formula, press enter. The value returned, in this case, is 8GB for the price of $1300.
Let’s proceed to the next section and understand how to use VLOOKUP for multiple criteria.
How to Use VLOOKUP for Multiple Criteria?
With a helper column, you can give multiple criteria that the VLOOKUP genetically cannot handle. In our example, we will be calculating the price based on both company and product.
For example, if we have to look up the price of an Apple MacBook Pro, where the company name and product name are in two different columns, we use a helper column. This column will store the concatenated values of both columns.
Follow the steps below to perform VLOOKUP with multiple criteria.
- First, right-click on a column header and click on Insert. This will help you insert a column to the left of the Company column. Name it as ‘Company & Product’.
- On creating the helper column, enter the formula =C2&”-”&D2. Then, drag the formula down to the rest of the cells in the column.
- On creating the concatenated column successfully, we can now look for the value. Here, we look for the price of an Apple MacBook Pro.
- So, we enter the formula =VLOOKUP(B15&”-”&C15,B1:G11,5,FALSE)
- The first parameter helps to concatenate the lookup value, i.e., B15&"-" &C15. We can also use the concatenate formula, i.e., CONCATENATE(B15,"-",C15). The second parameter is the table range. The third parameter specifies the column index, which returns a value from that column. The final parameter is FALSE, as we are looking for an Exact Match. On pressing enter, the price will be returned as follows.
FREE Business Analytics With Excel Course
Start your Business Analytics Learning for FREEStart Learning
Tips to Use VLOOKUP Function Efficiently
When we enter an incorrect formula or add a wrong value somewhere the VLOOKUP function shows an error. Here are some tips that you must keep in mind while using VLOOKUP function -
- If you omit range_lookup from the VLOOKUP function, it will evaluate the non-exact matches as well. However, it will use an exact match if it exists in your data.
- If the lookup column has duplicate values then it will only pick the first value.
- VLOOKUP is not case-sensitive.
- Adding a column after applying VLOOKUP formula will show an error as hard-coded index values don’t change automatically on adding or deleting columns.
Common Errors in VLOOKUP Function
- #N/A! error – Occurs if the VLOOKUP function fails to find a match to the supplied lookup_value.
- #REF! error – Occurs if when the col_index_num argument > number of columns in the supplied table_array; or the formula attempts to reference cells that do not exist.
- #VALUE! error – Occurs if either: The col_index_num argument is less than 1 or is not recognized as a numeric value; or The range_lookup argument is not recognized as one of the logical values TRUE or FALSE.
This is all you need to know about VLOOKUP in Excel.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!
Conclusion
This article has covered a tutorial on how to use VLOOKUP in Excel for Exact and Approximate matches. You have also learned how to use the VLOOKUP function when you have multiple criteria. You need to carefully understand and remember the parameters used to perform searching tasks in Excel.
We hope this article helps in paving your way to a fruitful career by honing your Excel skills. If you have any questions, please mention it in the comments section, and our experts will get back to you right away.
You can also go the extra mile and enhance your Excel skills by enrolling in the Business Analytics with Excel Free Course offered by Simplilearn.
