In this guide we're going to go over 10 things you should know about the frequently-used-yet-not-so-easy-to-use VLOOKUP Excel function. Although this guide mainly focuses on the VLOOKUP function, these tips also apply to the HLOOKUP function, which essentially does the same thing horizontally. Fore more information about the HLOOKUP function, please see Function: HLOOKUP
1. How it works
VLOOKUP Excel function simply searches for a given value in a table, and returns the data from a specific section of that table. The function works in "vertical orientation" (the name stands for "vertical lookup"). For this, you need to arrange your table vertically as well. A vertical table contains data in the rows and each row represents a set of data.
VLOOKUP requires 4 arguments to execute:
VLOOKUP searches the lookup value in the first column of the table array. If lookup value is found in table, VLOOKUP returns the corresponding value as specified by the column index number. The last argument, range lookup, is optional and determines the search method type.
The example above consists of a 3-column table (B6:D20) and the formula in C3 retrieves the information from the 3rd column of the table for the value in C2.
2. VLOOKUP has two modes
The range_lookup argument can take a TRUE or FALSE value, and this input determines the type of matching. There are two modes: approximate match and exact match. Use TRUE or 1 for an approximate match, and FALSE or 0 for an exact match.
Exact Match
Use the exact match mode if you want to search for a value in a table containing unique values. The previous example is also a demonstration of an exact match, since all of the values in the first column are unique. To do an exact match search, use FALSE for the range_lookup parameter:
VLOOKUP(lookup_value, table_array, col_index_num, 0)
Approximate Match
The approximate match mode is useful for searching values between ranges. Finding a tax rate is a good example for this type of scenarios. Tax rates are typically determined for intervals. For example, 14% for amounts between 25,000 and 50,000. Because you cannot prepare a table for each possible amount between interval limits, you need something that allows you to locate the value in the interval. In approximate search mode, VLOOKUP searches the closest value that is less than or equal to the lookup value.
In the example below, the lookup value is 36,000. However, the table in B4:C9 doesn't not have this value. Since approximate search is active, VLOOKUP locates the closest smaller value (25,000) and returns 14% from the second column.
The range_lookup argument should be TRUE or omitted for an approximate search:
VLOOKUP(lookup_value, table_array, col_index_num, TRUE)
VLOOKUP(lookup_value, table_array, col_index_num,1)
Notes for the approximate match mode
- The default mode of the VLOOKUP Excel function is approximate match.
- The first column of the table, where lookup value is searched should be sorted in ascending order.
- If the lookup value is smaller than the first item in the first column of the table, VLOOKUP returns #N/A error.
3. VLOOKUP only works to the right
VLOOKUP Excel function can only search in the first column of the table array and return the value to its right. This is one of the biggest limitations of the VLOOKUP function. Using zero (0) or negative numbers for the column index number causes the function to return #VALUE! error.
Instead, use the INDEX and MATCH combination if this is an issue. See this page for more details: INDEX & MATCH: A Better Way to Look Up Data
4. VLOOKUP is not case-sensitive
The lookup value or strings in table array are not case sensitive. For the VLOOKUP Excel function, both "Spider-Man" and "sPiDeR-mAn" will give the same results.
5. VLOOKUP finds the first match
If there are multiple occurrences of the lookup value in the table array, VLOOKUP returns the corresponding data for the first matched row.
In the example below, there are two cells in the table containing "Iron Man, Tony Stark". VLOOKUP returns the height data for the first occurrence. As a result, when you get an unexpected result, you may want to check for duplicate data.
6. VLOOKUP supports wildcards
This is one of the least known, yet best features of the VLOOKUP Excel function. You can use wildcard characters to search without actually entering the entire lookup value. A question mark (?) or an asterisk (*) act as a placeholder for single and multiple characters, respectively. However, note that using strings with wildcards can mean more than one match value. For example, "S*man" string can mean either "Superman" or "Spider-Man". When this happens, VLOOKUP will find the first match, just like in previous example.
If the table actually contains values with a question mark (?) or an asterisk (*), you need to use the tilde (~) character to eliminate the wildcard effect. Simply place a tilde (~) before those characters to search a string containing (?) or (*).
| Operator | Description | Criteria Sample | Criteria Meaning |
| ? | Takes the place of a single character | “Ala?azam” | 8-character word starts with “Ala” and finishes with “azam” |
| * | Can take the place of any number of characters. | “Ala*” | Any number of character word starts with “Ala” |
| ~ | Use tilde in front of a question mark or an asterisk to actually find them | “Ala~*” | Equal to “Ala*” |
7. Numbers as text can cause a match error
If you search for numeric values and see an unexpected #N/A error from the VLOOKUP function, this tip might come in handy. Although Excel can guess the type of values, you might sometimes need to match the data types by either converting the lookup value into a string or the lookup column into number.
The first option is relatively easier, because you need to deal with a single value. To convert a number into a string you can add an empty string to the number or use the TEXT function.
=VLOOKUP(TEXT(D2,"0"),B8:E22,4,FALSE)
8. Inserting or deleting a column may break existing VLOOKUP formulas
Using a constant column index numbers may cause problems when you want to insert or delete a column in the table. For example, let's say your formula is looking at the 3rd column, and you insert a column between 1st and 2nd columns. In this case, the column the formula was targeting would be shifted to the right and will become the 4th column.
To overcome this issue, you need to make the column index number dynamic using other formulas, and there are number of ways to do this. In this example, we used the MATCH function to return the position of the desired column as a column index number.
9. Two approximate VLOOKUPS are faster than one exact VLOOKUP
The exact match mode actually requires much more resources than an approximate match. Approximate match uses binary search and assumes that your table is sorted, and starts the search from the beginning of your table. On the other hand, the exact match method evaluates the entire table at once.
A single approximate match VLOOKUP can't be precise like its exact match version. You can overcome this using two approximate search VLOOKUPs. The two-VLOOKUP method first checks whether the lookup value exists in the first column only. If so, the second VLOOKUP retrieves the data from the table.
VLOOKUP(lookup_value, table_array, col_index_num, True), “not found”)
Before using the 2-VLOOKUP approach, make sure that your lookup column is sorted and in ascending order.
10. Prefer using the INDEX and MATCH combination
Without a doubt that VLOOKUP is one of the most popular formulas of Excel, and also see our VLOOKUP guide to learn more about its basics and more examples. Although, it is practical and easy-to-use, the function has its limitations. For more versatile lookup actions, we recommend using the combination of INDEX and MATCH functions instead. With this, you can have complete control on locating the data.
Here is a simple example how you can retrieve the "Height" data for "Thor":
