The Excel VLOOKUP function looks up values in a table and returns the corresponding value from another table. The VLOOKUP function is one of the most popular functions in Excel, and for a good reason – it’s incredibly useful! This article will explain what Microsoft Excel VLOOKUP is used for and how to use it with approximate or exact match examples. We will also provide a step-by-step guide on how to use the VLOOKUP formula. Let’s get started!
Table of Contents
- What is the VLOOKUP function in Excel?
- The VLOOKUP Formula
- How to do a VLOOKUP in Excel
- How can I find an exact match using VLOOKUP?
- How can I find an approximate match using VLOOKUP?
- The Lookup Value: when to use Exact Match versus Approximate Match
- How to gracefully handle N A error returned by VLOOKUP
- How can I use VLOOKUP for multiple criteria?
- Using wildcards in the VLOOKUP formula
- What data Types can the VLOOKUP function return?
- How to do VLOOKUP in Excel with two spreadsheets
- Tips for using VLOOKUP Function Effectively
- Conclusion
- Recommended Articles
What is the VLOOKUP function in Excel?
The VLOOKUP function is a built-in function in Microsoft Excel to look up values in a table.
The function takes four arguments – the lookup value, table array, column index number, and range lookup. The VLOOKUP in Excel looks for a value in the leftmost column of a table array and then returns a corresponding value in the same row from a column you specify. When using the approximate match option, you must sort the table array in ascending order. The VLOOKUP function performs a search as a vertical lookup in the table range.
The VLOOKUP Formula
The VLOOKUP formula consists of four function arguments:
Lookup_value:
The value in the table’s first column can be a value, a reference, or a text string.
Table_array:
The table is any range of text, numbers, dates, etc., from which data is retrieved. The table array can be a reference or a range or range name.
Col_index_num:
The column number in the table from which the matching value is returned. The column index of the first column of values in the table is “1”.
Range_lookup:
The range lookup is a logical value of TRUE or FALSE.
TRUE for the closest match in the first column sorted in ascending order.
FALSE to find a precise match.
How to do a VLOOKUP in Excel
Now that we know what the VLOOKUP function is used for let’s look at how to use it in the following formula examples below.
Using the VLOOKUP function is easy – enter “=VLOOKUP” into a cell, followed by the arguments. Microsoft Excel will show some VLOOKUP formula hints as you fill out the four function arguments until you close the function after the final parameter with a closing bracket and press enter.
The function’s first argument is the lookup value and can be an actual value or a reference. Actual text values should be enclosed with quotation marks like this:
In the case of numeric lookup values, you should use quotes:
Cell references can also be used in VLOOKUP syntax as follows. Here the cell reference is E2, as shown below.
The second argument is the table range that contains the data to be searched. Table ranges can be entered as absolute references or named range references in the VLOOKUP formulas.
The third parameter is the column number relative to the table range that contains the lookup value that needs to be returned by the VLOOKUP. In the example above, the Grades (column C) is the lookup column and is column 3 relative to the selected table range. This column number changes depending on your VLOOKUP objective and should not be confused with the left most column relative to the table range. The latter contains the vertical lookup values against which the first parameter performs its search.
How to use VLOOKUP in Excel depends on your matching objectives. There are two ways to use VLOOKUP in Excel – approximate match and exact match. The logical value determines match mode in the last argument of the VLOOKUP function. If the fourth argument in the VLOOKUP formula is TRUE, it will less likely return an N A error. However, the return value may be inaccurate if the user is not fully aware of how to properly use the approximate match mode in the VLOOKUP function.
Continue reading to learn more about TRUE vs. FALSE and VLOOKUP matching options next.
How can I find an exact match using VLOOKUP?
Exact Match:
The first way to use VLOOKUP is for exact matches. An exact match means you are looking for a value equal to the value you are looking up. This match mode is the most common way to use VLOOKUP in Excel.
To use VLOOKUP for an exact match, you need to specify the following arguments:
– The lookup value
– The table to look in
– The column number in the table to return the value from (third argument)
– FALSE (for exact match)
For example, let’s say you have a list of students and grades and want to look up the grade for a particular student. The student’s name is on the list, and you want to find an exact match.
Consider the blue table below with a list of Student names with corresponding grades.
You can use VLOOKUP to find an exact match. Let’s add a VLOOKUP reference in cell E2 that we will use to look up and display the grade of any student name that we will type into cell D2. Select cell E2 and type in “=VLOOKUP” to see the VLOOKUP formula hints display.
Continue to type the opening bracket “(” to see the formula argument hints and current argument to fill out.
Continue to enter all four arguments and the closing bracket and press enter to complete the formula. In this Excel VLOOKUP example below, we use a VLOOKUP to look up the grade for the student “Emma” from the blue table and display her grade in the green area in cell E2.
With the exact match, if the lookup values do not exist in the leftmost column of the lookup table (blue above), then the VLOOKUP will return an N A error which means the function found no exact match lookup value.
Tip: the N A error can be gracefully handled and displayed as a blank or another text. See an example later in this article.
If we change the lookup value from “Emma” to “Jim,” it will return no matched value from the first column in the table array, as shown below:
If we use the Excel VLOOKUP function to perform a perfect match on “Jack,” it will return the first match on the name Jack based on the order from top to bottom, and the function will return “A” from the left most column.
In the case of Jack above, we see that we have duplicate values, so if we want to search for the second Jack to get the return value of Grade C, we will need to add a unique identifier for searching. The lookup column should contain a unique lookup value like StudentID.
So if we add StudentID as the new leftmost column and have Student as the second column, then we will have a unique value to search for to look up the corresponding grade for the second Jack that has the C grade (in the above examples).
The first column does not need to be sorted with the precise matching mode.
How can I find an approximate match using VLOOKUP?
Approximate Match:
The second way to use VLOOKUP is for approximate matches. An approximate match means that you are looking for a value close to, but not necessarily equal to, the value you are looking up. Typically this would apply to a situation where you are looking for numeric value within a quantity range, for example, a price list where you get volume discounts depending on how many units you want to order.
VLOOKUP formulas are easy to work with but can give unexpected results if you do not understand how the approximate matching logic works or forget to sort the table data in ascending order on the first column.
To use VLOOKUP for an approximate match, you need to specify the following arguments:
– The lookup value
– The table to look in
– The column index number in the table to return the vertical lookup value from
– TRUE (for an approximate match, table range in ascending order)
To demonstrate the approximate match mode of the Excel VLOOKUP function, let’s look at a simple Quantity Price table range (blue) and a quantity and price from the quantity price table. Remember, as mentioned earlier, making the search value in the lookup column a numeric value for approximate mode is best. Otherwise, the specified value may not return the correct corresponding data value.
The above image shows that using logical values TRUE as the fourth argument will ensure that the VLOOKUP function returns the closest match within the vertical lookup data range. When the specified Exact Quantity in cell D2 is greater or equal to 1 (but less than 5), the VLOOKUP works and returns the Price of $25. If the lookup value were 9, it would fall between the 5 to 10 range and return $23.
And a lookup value greater than 300 would match against the closet match in the table range and the corresponding data in the second column returns $11.
Let’s say in the previous example that we change the logical value in the final parameter from TRUE to FALSE in the Excel function. The VLOOKUP function fails and returns an N A error because there is no perfect match for the exact quantity value of 500 in the leftmost column of the data range table (blue).
The Lookup Value: when to use Exact Match versus Approximate Match
If your lookup value needs to be an exact value (for example, if you’re looking up a product ID), then you should use an exact match. The exact match will ensure that you get an accurate result. On the other hand, if your lookup value is approximate (for example, if you’re looking up a quantity within a quantity pricing table range), then you should use an approximate match to return the closest quantity match.
So what’s the difference between an exact and approximate match? Exact match lookup values must be an exact match for the lookup value for the formula to return a result. Approximate lookup values can be close but don’t have to be an exact match.
The VLOOKUP examples above will clarify the difference and how to use the logical value of TRUE or FALSE in Excel’s VLOOKUP function.
How to gracefully handle N A error returned by VLOOKUP
Below is an example where the exact match VLOOKUP function could not find the precisely matching value and returns the N A error. “#N/A” does not look professional or elegant.
Wrapping the VLOOKUP function inside the IFNA function allows you to show another value instead of the ugly “#N/A.”
=IFNA(VLOOKUP(E2,STUDENTS,3,FALSE),”-“)
It looks more elegant, displaying a hyphen instead of the N A error:
How can I use VLOOKUP for multiple criteria?
Natively, the VLOOKUP function does not accept multiple criteria as lookup value input arguments (only one input parameter in the VLOOKUP formula exists for the lookup value). The table range vertical lookup column is also limited to just one column.
The VLOOKUP works with multiple criteria by concatenating multiple lookup values in the first argument and then matching that search value against the leftmost column (which should contain the same concatenated order values).
If that data is not part of the original table range, one can create a helper column with the same concatenated values and make it the first column.
In the example below, imagine instead of Student’s first name, we have Student First Name and Student Last Name in the range, and we need to use a VLOOKUP to search on both first and last names as multiple criteria.
Because VLOOKUP only accepts one search input parameter, we concatenate the first and last names as a single combined lookup value. Also, because the VLOOKUP function can only search against one single column in the range, we need to create a helper column to serve as the lookup column that contains the Student First Name and Student Last Name as concatenated values.
Remember, if we add more columns to the table range, we also need to change the value of the third parameter in the VLOOKUP function to reference the column that contains the return data. In this case, Grade column D is now column 4:
Using wildcards in the VLOOKUP formula
Look up text containing or starting or ending with certain characters.
You can use wildcards in the VLOOKUP formula to find a partial match. You can use wildcard characters to stand for unknown or variable values. To use a wildcard character with the VLOOKUP function, you would use the asterisk “*” or “?” and combine it with the lookup value using the ampersand “&” concatenation symbol we used in the multiple criteria VLOOKUP examples above.
“*” is used for one or more characters length
“?” is used for one character length only
When using a wildcard character in the VLOOKUP function, set the last argument to FALSE for an exact match.
“Begins with” VLOOKUP search:
If you add the asterisk after the lookup value, it will search for the first partial match beginning with the specified value. E.g., “Jack A”
“Ends with” VLOOKUP search:
Like the above, you can place the asterisk before the value to create an “ends with” VLOOKUP search.
“Contains” VLOOKUP search:
If you add the asterisk before and after the value, it will search for partial matches that contain the value. E.g., “ack.” This example shows multiple matches on “ack” because we have two students named Jack. The VLOOKUP function will return the first match it finds.
What data Types can the VLOOKUP function return?
The VLOOKUP function can return any data, including dates. However, the problem arises when the first argument value is, say, of type general while the range reference contains values of type text. In this case, if you use VLOOKUP, it will not be able to find a match in the corresponding data. Here is an excellent post from Excel University on how to use the VLOOKUP function with different data types.
How to do VLOOKUP in Excel with two spreadsheets
So far, all our examples of how to use VLOOKUP in Excel have focused on working on one worksheet.
How to do a VLOOKUP between two sheets in Excel?
If you know how to use the VLOOKUP function with reference cells, then you know how to use VLOOKUP in Excel with two sheets. In the following formula example below, we will change the second parameter (from our earlier examples above) to point to another sheet.
In the second parameter of the VLOOKUP function, add the sheet name plus the exclamation mark “!” before the cell reference range.
=VLOOKUP(D2,Sheet2!$A$2:$B$11,2,TRUE)
Alternatively, as you fill out the formula parameters, you can navigate to the sheet that contains the range and select the cell range followed by a comma to move on to the following parameters, add a closing bracket, and “enter” when done.
How to VLOOKUP between two workbooks in Excel?
It is also possible to use the VLOOKUP function between two workbooks. In our following example, let’s move the data to search from the second sheet into a separate workbook file. Let’s name the second workbook file “Book2.xls” in our example.
Next, in the second parameter of the VLOOKUP function, start by typing an opening square bracket, followed by the workbook file name, and then a closing bracket. Then add the sheet name plus the exclamation mark “!” before the cell reference range.
=VLOOKUP(D2,[Book2.xlsx]Sheet1!$A$2:$B$11,2,TRUE)
Alternatively, as you fill out the formula parameters, you can navigate to the workbook and sheet that contains the range and select the cell range followed by a comma to move on to the next parameters, add a closing bracket, and “enter” when done.
Tips for using VLOOKUP Function Effectively
Here are some tips to help you use the VLOOKUP function effectively:
– Make sure the value you are looking up is in the first column of the table you are looking in.
– The order of the columns in the table does not matter. However, the order of the rows does matter if you use an approximate match.
– Use an exact match when possible, as the approximate match can sometimes return incorrect results due to user error.
– Use the Excel VLOOKUP function with caution when working with large data tables, as it can slow down your computer.
– Use wildcards (* and ?) if you are unsure of the spelling of a value.
For example, “*Smith” would find “John Smith.”
– You can use the VLOOKUP function with other functions, such as “IF” and “IFNA.”
– Use named ranges when working with multiple sheets, as this will make it easier to change your formulas if the structure of your data changes.
Conclusion
Knowing how to use VLOOKUP in Excel will add a powerful tool to your arsenal that can save you time and energy when working with large data sets in Excel. Working with the VLOOKUP formula in Excel is easy! Following the examples and tips above, you can work with VLOOKUP more effectively and get the most out of this useful function.
