VLOOKUP Excel: Extended Uses, Examples, and Limits

Formulas & functions · Updated November 18, 2026

VLOOKUP can do more than retrieve one value from a simple list: it can use exact or approximate matches, wildcards, multi-part lookup keys, and a calculated return-column number. With product IDs in A2:A100, prices in C2:C100, and the ID to find in E2, use =VLOOKUP(E2,$A$2:$C$100,3,FALSE); if E2 contains P-104 and that row’s price is 18.50, the formula returns 18.50.

What VLOOKUP Does and How Its Arguments Work

VLOOKUP stands for vertical lookup. It searches down the first column of a selected range, finds a matching value, then returns a value from another column on the same row. Its syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The bracketed final argument is optional, but leaving it out asks Excel to use approximate matching, which can produce an unexpected result. For most ordinary record searches, specify FALSE for an exact match.

The arguments determine what Excel searches and what it returns:

  • lookup_value: The value to find, such as a product code in E2. It can be a cell reference or a value typed into the formula.
  • table_array: The range containing both the search column and the return column. VLOOKUP searches only the range’s leftmost column.
  • col_index_num: The return column’s position counted from the left edge of table_array. In A:C, column C is number 3, not worksheet column number 3 in every possible range.
  • range_lookup: FALSE requests an exact match; TRUE requests an approximate match. FALSE and 0 mean the same thing, as do TRUE and 1.

For a reliable exact lookup, lock the table range with dollar signs before copying the formula down. That keeps the search range fixed while E2 changes to E3, E4, and so on. You can also copy the formula down with the fill handle. The dollar signs do not change the lookup result; they prevent the reference from shifting.

Exact Matches for Everyday Lookups

Exact matching is the best starting point for looking up unique identifiers such as employee numbers, invoice IDs, or SKU codes. With FALSE, Excel returns a result only when it finds a matching value in the first column. If there is no match, the result is usually #N/A. Exact matching does not require the first column to be sorted.

VLOOKUP returns the first matching row it encounters, so duplicate keys matter. If the same product ID appears several times with different prices, the formula returns the price from the first matching row, not a total or the most recent price. Clean or investigate duplicate identifiers when each key is supposed to represent one record.

Exact matching is not case-sensitive: codes such as ab12 and AB12 are treated as equivalent for this lookup. It can also fail when values that look alike are stored differently. For example, the number 104 and text “104” may not match as expected, and a trailing space in a text code can prevent a match. Check the source data’s type and spacing when a visible match returns #N/A.

Approximate Matches for Bands and Thresholds

Approximate matching is useful when a value belongs to a band rather than needing an identical row—for example, assigning a grade from a score threshold or a shipping tier from an order total. With score 73 in B2 and thresholds 0, 60, 70, 80, and 90 in ascending order in E2:E6, with labels in F2:F6, use =VLOOKUP(B2,$E$2:$F$6,2,TRUE). If the thresholds map 70 to C and 80 to B, a score of 73 returns C, the label for the largest threshold not greater than 73.

For approximate matching, the first column of the lookup range must be sorted in ascending order. If it is not, the result can be wrong rather than clearly reporting that the data is unsorted. A value below the smallest threshold returns #N/A. Review boundary values carefully: a threshold of 70 means that 70 itself belongs to the 70 band, while 69.99 belongs to the previous band. If you need an exact match for a threshold table, use FALSE instead.

Wildcards for Partial Text Matches

With FALSE, VLOOKUP can use wildcards to find text that is not a complete literal match. An asterisk represents any number of characters, while a question mark represents one character. A tilde escapes a wildcard when you need to search for the character itself: for example, ~* searches for a literal asterisk. Wildcards work with text lookups; they are not a general way to find a partial numeric value.

With search text in E2 and names or descriptions in A2:A50, with the value to return in B2:B50, use =VLOOKUP("*"&E2&"*",$A$2:$B$50,2,FALSE) to find a cell containing the text from E2 anywhere within its first-column value. If E2 contains “cable”, this can match a description such as “USB cable, 2 m”. The formula returns the first matching row, so it is not suitable when you need every result containing that text.

Returning a Column Chosen by Its Heading

A fixed column number can become fragile when someone inserts or rearranges columns in the source range. You can calculate the return-column number from a heading instead. Suppose the lookup key is in column A, the table headers are in A1:D1, the data occupies A2:D100, the key to find is in G2, and the requested return heading is in H1. Use =VLOOKUP($G2,$A$1:$D$100,MATCH(H$1,$A$1:$D$1,0),FALSE). MATCH finds the position of the heading in the header row, and VLOOKUP uses that position as its column index.

This method is useful for reports where a user selects a field such as Department or Status and expects the formula to return that column. The heading must appear in the header row exactly as written in H1, and it must be within the table range. If MATCH cannot find it, the combined formula returns #N/A. Duplicate headings are also ambiguous because MATCH returns the first match.

Looking Up Records with More Than One Condition

VLOOKUP accepts one lookup value, but a helper key can combine two fields into one. Suppose a table has department codes in A2:A100, employee IDs in B2:B100, and names in C2:C100. Create a combined key in a new first column, such as department plus a separator plus employee ID, and use the same construction for the lookup value. If the helper key is in D2:D100, the employee ID is in F2, and the department is in G2, use =VLOOKUP(G2&"|"&F2,$D$2:$E$100,2,FALSE) when column E contains the name to return.

The separator helps avoid accidental collisions: combining 12 and 34 without a separator produces the same text as combining 1 and 234. Choose a separator that cannot occur in either field, or otherwise design the key so different pairs cannot create the same combined text. Both the stored helper key and the lookup expression must use the same order and construction. This approach is practical for a conventional VLOOKUP workflow, but it adds a helper column that must be maintained.

Handling Missing Results and Common Errors

#N/A means Excel did not find a usable match, but the cause may be a missing key, a type mismatch, extra spaces, or an incorrect search range. #REF! can occur if the column index is larger than the number of columns in the selected range. A result from the wrong field often points to an incorrect column index or a range that starts in the wrong place.

To display a friendlier message when a lookup has no result, with the same product layout as the opening example, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). IFNA catches #N/A specifically, leaving other errors visible for diagnosis. If you instead want to replace any error with a message, use =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Check lookup"). IFERROR can conceal a broken reference or other problem, so use it when that broad behavior is intentional, not merely to hide unexplained errors.

When troubleshooting, check that the lookup value is in the first column of the selected range, the return column is included, and the column index counts from the range’s left edge. Also verify whether IDs are numbers or text and whether hidden spaces are present. For an exact lookup, sorting is not required; sorting is essential for approximate matching.

VLOOKUP Compared with XLOOKUP and Other Options

VLOOKUP remains useful in older workbooks and is familiar to many spreadsheet users, but its leftmost-column rule is a real limitation: it cannot search a column and return a value from a column to its left. Its numeric column index can also become outdated if the table’s structure changes. A helper column or a different lookup function may be a better fit when those constraints cause trouble.

XLOOKUP, available in Microsoft 365 and Excel 2021 or later, can search one range and return from a separate range, including a range to the left. With product IDs in A2:A100, prices in C2:C100, and the requested ID in E2, use =XLOOKUP(E2,A2:A100,C2:C100,"Not found"). This returns the matching price or the stated message if there is no match. INDEX with MATCH is another established option, including in older Excel versions, when you need a lookup that can return from either side of the search column. If a workbook must be opened in older Excel editions, confirm that the functions you use are supported there.

Frequently Asked Questions

Does VLOOKUP search to the left?
No. It searches the first column of table_array and returns a value from a column to its right. Use XLOOKUP, INDEX with MATCH, or restructure the source range if the desired return field is left of the search field.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact lookup, which is the usual choice for IDs and names. Use TRUE for an approximate lookup only when the first column is sorted in ascending order and you want the closest value that does not exceed the lookup value.

Why does VLOOKUP return #N/A when the value is visibly there?
Common causes include text-versus-number differences, hidden leading or trailing spaces, a lookup range that starts in the wrong column, or a value that differs by a character. Confirm the data types and compare the actual cell contents, not only how they appear on screen.

Can VLOOKUP return multiple matching rows?
Not by itself. It returns one result—the first match it finds. In Microsoft 365 or Excel 2021 and later, FILTER can return multiple matching rows; in older versions, use an appropriate filter, helper process, or other formula approach.

Does VLOOKUP work when columns are inserted?
It may still work, but a fixed column index can point to a different field after columns are rearranged within the lookup range. Finding the return column with MATCH, using a structured table design, or switching to XLOOKUP can make the intent clearer and reduce that risk.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.