VLOOKUP searches the first column of a table and returns a value from another column in the same row. Use FALSE for an exact match, such as a student ID. Use TRUE for a sorted threshold table, such as quantity discounts.
VLOOKUP works in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016, as well as Excel for the web. The FILTER example later in this article needs Microsoft 365, Excel for the web, Excel 2021 or Excel 2024. Microsoft’s VLOOKUP documentation explains the function’s arguments and limitations.
A Small Exact Match Example
Enter these headers in A1:C1 and the data in A2:C5 on a blank worksheet. Put 103 in F2.
| A: ID | B: Name | C: Department |
|---|---|---|
| 101 | Ana | Sales |
| 102 | Ben | Finance |
| 103 | Cara | Sales |
| 104 | Dan | IT |
=VLOOKUP(F2,$A$2:$C$5,2,FALSE) returns Cara. Column 2 means the second column inside A:C, which is B. With 999 in F2, the formula returns #N/A. Exact matching does not require sorting; if IDs repeat, it returns the first match.
When to Use TRUE for Approximate Matching
Enter this table in H1:I5, with discounts stored as percentages. Sort the minimum quantities in ascending order and keep each discount on the same row as its threshold.
| H: Minimum quantity | I: Discount |
|---|---|
| 0 | 0% |
| 10 | 5% |
| 20 | 10% |
| 50 | 15% |
=VLOOKUP(17,$H$2:$I$5,2,TRUE) returns 5%: 10 is the largest threshold that does not exceed 17. It does not choose the mathematically nearest threshold. A lookup of -1 returns #N/A because it is below the lowest threshold. If you omit the fourth argument, VLOOKUP uses TRUE; specify FALSE when you need an ID or name match.
Troubleshooting Wrong Results and Errors
For a wrong result, check whether TRUE was used on unsorted data or omitted accidentally. For #N/A, compare numeric IDs with numbers stored as text and inspect extra spaces. For #REF!, make sure the column index fits inside the table. Use absolute references to keep the table fixed when copying the formula.
VLOOKUP cannot normally return a column to the left of the lookup column. Use XLOOKUP in a supported version or INDEX and MATCH. If a formula unexpectedly spills, check that the lookup value is one cell, rather than an entire column. See the #SPILL! troubleshooting guide.
Primary VLOOKUP Function
The VLOOKUP function is used to search for a certain value in the leftmost column of a table and return the corresponding value from any other column in the same row.
The basic syntax of the Vlookup function is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Here lookup_value is the value you want to look up in the first column of the table. The table_array is the table that you want to search in, and col_index_num is the column number in the table from which you want to return the corresponding value.
The range_lookup is an optional argument that specifies whether you want an exact or approximate match. Excel will look for an approximate match if this argument is set to TRUE or omitted. If this argument is set to FALSE, Excel will look for an exact match.
Let’s want to find the information of a student with roll number 2. Here’s how you can do it using Vlookup:
- Select a cell where you want to display the result. Let’s say you want to find the first name of the student. For this, type the following formula: =VLOOKUP($J$4, $A$2:$D$13, 2, FALSE). (The $ sign is added to indicate an absolute reference).
- In this formula, we are using the J4 as the lookup_value since we want to find information about the student with roll number 2, which is in this cell.
- The table_array argument is set to A2:D13, which specifies the range of cells that contains the table we want to search in.
- The col_index_num argument is set to 2, which specifies that we want to return the corresponding value from the second column of the table (i.e., the Name column).
- Finally, the range_lookup argument is set to FALSE since we want an exact match. Press Enter to display the result.
The result will show the first name of the student with roll number 2. If you want to find other information, just change the col_index_num argument to the appropriate column number.
Exact vs Approx Match
The lookup function can be used with both exact and approximate match modes, depending on the type of data being searched.
An exact match uses FALSE. In the discount example, an exact quantity present in the table returns its corresponding discount. A quantity absent from the table returns #N/A.
The formula will =VLOOKUP($B2, $L$8:$M$13, 2, FALSE) for our example. It will return the percentage for exact values.
However, you will find that it returned an error for the different percentages.
For an approximate match, use TRUE and sort the first column in ascending order. VLOOKUP returns the row with the largest threshold less than or equal to the lookup value. A value below the first threshold returns #N/A.
To change the formula from exact to approximate matching, replace FALSE with TRUE after sorting the quantity thresholds in ascending order.
Wildcard
With FALSE, VLOOKUP supports wildcards in text lookups. An asterisk (*) matches any sequence of characters; a question mark (?) matches exactly one character. Use ~* or ~? to match a literal asterisk or question mark. In the small A1:C5 sample above, =VLOOKUP("Ca*",$B$2:$C$5,2,FALSE) returns Sales.
To put this into practice, let’s say we will need to find a student name with the first few characters of their name. We can start by entering the lookup value and the table area into the VLOOKUP function. Then, add the wildcard operator (in this case, the asterisk) by using the ampersand operator (&) and enclosing the wildcard in quotes. Finally, close the parenthesis and press enter to see the result.
The formula for the wildcard match for our given example will be =VLOOKUP($J$2&”*”,B2:D13,1, FALSE).
This formula will return the first match (student name) for any text string in cell J2. The limitation of the wildcard match is that it will only return the first match.
Filter Function
If you want to return multiple matches when performing a lookup, then the filter function will be more useful for you.
For example, suppose you have a table of data with student names and the highest run by them. And there are multiple values for the same student. Let’s say you want to find the highest run values for Joshua.
The formula for the filter function is =FILTER(array, include,[if_empty]).
In this example, the array is A2:B15 and the condition is $A$2:$A$15=J3, where J3 contains Joshua. Use =FILTER(A2:B15,$A$2:$A$15=J3,"Not Found") to return every matching row. FILTER does not calculate a maximum; it returns the recorded values for Joshua.
The results spill into the cells below and beside the formula. Clear any obstructing cells, and keep the formula outside an Excel Table.
Two Way Lookup (VLOOKUP and MATCH)
VLOOKUP and MATCH can retrieve a value using both a row label and a column heading. MATCH finds the return column number for VLOOKUP.
Let’s learn it through an example. Here we have a sheet with sales data from different years. Now we want to look up the sales of 2021 for an SR named Joshua Kimmich.
We will use the formula = VLOOKUP($H$2,$A$2:$D$6,MATCH($H$3,A1:D1,0),FALSE).
This formula searches for the SR name in H2 (Joshua Kimmich) and then looks up the value of 2021 from the table using MATCH(H3,A1:D1,0). This will return the sales amount for Joshua Kimmich in 2021.
This example combines VLOOKUP with MATCH. INDEX and MATCH is a separate alternative that also supports lookups to the left; it is not the formula used here.
VLOOKUP Errors
A missing exact match returns #N/A. IFNA can replace that error with a message for a missing record. IFERROR catches other errors too, including #REF!, so inspect the cause before using it to hide an error.
Check FALSE for exact matching or TRUE for sorted threshold matching before adding error handling. An error wrapper does not correct an invalid range or an unsuitable match mode.
Conclusion
Use FALSE for an ID lookup and TRUE for a sorted threshold table. Add wildcards for a text pattern, or use FILTER in a supported Excel version when you need all matching rows.
We have also discussed the ways to handle errors when using VLOOKUP in Excel. As long as you understand the different options available, you should be able to use this function to its full potential.
Good luck!
