To make VLOOKUP find an exact match in Excel, set its fourth argument to FALSE (or 0). For example, with the lookup value in F2 and a lookup table in A2:C5, use =VLOOKUP(F2,$A$2:$C$5,3,FALSE); Excel searches the table’s first column for that value and returns the corresponding value from its third column.
What an exact match means
VLOOKUP searches vertically: it looks down the leftmost column of a selected table range for a value, then returns a value from another column in the same row. The fourth argument, often called the range_lookup argument, controls whether Excel requires an exact match or may use an approximate one. FALSE tells Excel to return a result only when it finds a matching value in the first column.
Exact match is the safer choice for identifiers and categories: product codes, employee IDs, invoice numbers, names, and status labels. If the requested value is absent, VLOOKUP normally returns #N/A rather than silently returning a nearby value. That makes missing data visible instead of treating a similar entry as the right answer.
“Exact” has a specific meaning here. VLOOKUP does not distinguish uppercase from lowercase, so ABC and abc are treated as the same text for lookup purposes. And, as explained below, the asterisk and question mark can act as wildcards even when the fourth argument is FALSE. Exact match is not the same as a case-sensitive comparison or a literal character-by-character search in every circumstance.
How the formula is structured
VLOOKUP has four arguments: the value to find, the table range to search, the number of the column to return, and whether to use approximate matching. The lookup column must be the first column in the table range. The return-column number is counted from the left edge of that selected range—not from the worksheet’s column letters.
For example, if a selected range begins in column D, its first column is number 1, its second is number 2, and its third is number 3. The fourth argument FALSE requests an exact match; 0 means the same thing. Writing FALSE explicitly is usually clearest because it makes the intended behavior easy to recognize.
Do not leave out the fourth argument when you need exact matching. If it is omitted, VLOOKUP uses approximate matching by default. This default can produce a plausible but incorrect result if the first column is not sorted appropriately. An explicit FALSE avoids that ambiguity.
Set up an exact-match lookup
In Excel for Microsoft 365 on Windows, enter the formula in the cell where you want the answer, then press Enter. Before writing it, check that the lookup value is in a cell or expression you can reference and that the first column of the table range contains the values to search.
- Identify the lookup value, such as a product code typed into F2.
- Select a table range whose leftmost column contains the codes and whose other columns include the information you want returned.
- Count the return column from the left edge of that range.
- Enter VLOOKUP with FALSE as the fourth argument.
- Copy the formula to other rows if needed, using dollar signs to keep the table range fixed.
In the example formula, the dollar signs in $A$2:$C$5 make that range absolute. If you copy the formula down, the lookup reference F2 can change to F3, F4, and so on, while the lookup table stays in place. Without the dollar signs, the table range may shift as the formula is filled down.
A worked product-code example
Suppose A2:C5 contains a small price list: column A has product codes, column B has product names, and column C has prices. Cell F2 contains the code P-205. If that code appears in A2:A5 and its price in column C is 12.99, VLOOKUP returns 12.99. The third argument is 3 because column C is the third column in the selected A:C range.
With product codes in A2:A5, names in B2:B5, prices in C2:C5, and the requested code in F2, enter =VLOOKUP(F2,$A$2:$C$5,3,FALSE) in G2. For P-205, assuming its row lists 12.99 in column C, the returned result is 12.99. If F2 contains a code that does not occur in A2:A5, the result is #N/A.
To return the product name instead, the return-column number would be 2, because names are in the second column of the selected range. The lookup value must still be found in column A. VLOOKUP cannot search in column B and return a value to its left in column A; this is one of its key design limitations.
Common causes of a missing match
A value can look identical on screen but still fail an exact lookup. Often the difference is in the underlying data rather than the formula. When a correct-looking lookup returns #N/A, check these likely causes:
- Number versus text: the lookup value may be the number 205 while the table contains the text value “205,” or the reverse. Formatting a cell to look like a number does not necessarily convert text into a number.
- Extra spaces: a leading or trailing space in a code or name can make it different from the entry you expect. Spaces may be hard to notice in a cell.
- Different characters: a hyphen, apostrophe, or other punctuation mark may differ, or a copied value may contain a nonbreaking space.
- Wrong lookup column: VLOOKUP searches only the first column of the specified range. If the key is in the second column, the range is set up incorrectly for VLOOKUP.
- Range moved while copying: a relative table reference may have shifted, so the formula now searches a different set of cells.
Compare the source and lookup values, and check their cell contents and data types. If the data comes from another system, decide whether it should be stored as text or as numbers, then normalize both the lookup value and the table consistently. For codes with leading zeros, such as 0047, text may be appropriate because converting to a number can remove those zeros.
Duplicates, wildcards, and case
If the first column contains the same lookup value more than once, VLOOKUP returns the result from the first matching row it encounters, starting at the top of the range. It does not report that duplicates exist or choose the latest record automatically. If the intended result depends on date, priority, or another condition, sort or filter the source data deliberately, or use a method designed to handle multiple criteria.
Exact-match mode is also not case-sensitive: a lookup for “north” can match “North.” If capitalization is meaningful in your data, VLOOKUP alone is not enough to enforce case sensitivity; an alternative formula using functions such as EXACT may be needed.
For text lookup values, an asterisk (*) can match any sequence of characters and a question mark (?) can match one character, even with FALSE. To search for a literal asterisk or question mark, put a tilde before the character in the lookup text. This matters when codes themselves contain these symbols: a value that appears to request an exact match may be interpreted as a pattern.
Exact versus approximate match
Approximate matching is useful in cases such as assigning a grade from score thresholds or looking up a tax band. It is not the right option for an ordinary code-to-record lookup. When approximate matching is used, the first column generally needs to be sorted in ascending order, and Excel can return the nearest lower value rather than an identical one. An unsorted list can lead to unexpected results.
For names, IDs, and product codes, use FALSE or 0. For thresholds, use approximate matching only when the lookup table is intentionally arranged for that purpose and you understand how boundary values behave. When in doubt, make the matching mode explicit rather than relying on VLOOKUP’s default.
When XLOOKUP may be a better option
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Unlike VLOOKUP, it can search one range and return a result from a separate range, including a column to the left of the lookup column. Its default match mode is exact, so a separate argument is not required to request exact matching. You can also specify a custom message for a missing value.
With product codes in A2:A5, prices in C2:C5, and the requested code in F2, enter =XLOOKUP(F2,A2:A5,C2:C5,"Not found"). If F2 contains P-205 and its price is 12.99, the formula returns 12.99; if no code matches, it returns Not found. VLOOKUP remains useful for compatibility with older Excel versions and existing workbooks, but XLOOKUP can be easier to maintain when the lookup and return columns are separate.
Frequently Asked Questions
What should I put in VLOOKUP for an exact match?
Put FALSE or 0 in the fourth argument. For example, with the lookup value in F2 and the table in A2:C5, =VLOOKUP(F2,$A$2:$C$5,3,FALSE) requests an exact match and returns the third column of that range.
Is FALSE the same as 0 in VLOOKUP?
Yes. Both request an exact match. FALSE is often easier for a person reading the formula to understand, while 0 is a shorter equivalent.
Why does VLOOKUP return #N/A when the value is there?
Check for number-versus-text differences, hidden spaces, punctuation differences, or a lookup value that is not in the first column of the selected range. Also confirm that the range has not shifted when the formula was copied.
Does VLOOKUP exact match distinguish uppercase and lowercase?
No. VLOOKUP is not case-sensitive, so text that differs only in capitalization is treated as a match. Use a case-sensitive approach if capitalization must be significant.
Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the leftmost column in the table range, and VLOOKUP returns values only from columns to its right. XLOOKUP can return from a separate range on either side of the lookup column.
What happens if VLOOKUP finds duplicate values?
It returns the result associated with the first matching row in the lookup range. If duplicates need different treatment, decide which record should take priority or use a lookup method that accounts for the additional criteria.
Related
- Excel Data Run Analysis: A Practical Runs Test
- Excel Data Model in the Web: What Works and What Doesn’t
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- WEEKDAY Function in Excel: Find the Day of the WeekExcel’s WEEKDAY function returns a number for the day of the week represented by a date. If cell…
- Excel Formula to Count Cells With a Value, Blanks, or TextTo count cells that contain something in Excel, use =COUNTA(A2:A20), replacing A2:A20 with the range you want to…
- Excel Formula for First Day of Month: ExamplesIf the date you want to use is in cell A2, enter =DATE(YEAR(A2),MONTH(A2),1) to return the first day…
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For…
- Unique in Excel: Get Values Without DuplicatesTo list each value once in Excel for Microsoft 365, enter =UNIQUE(A2:A10) in an empty cell, replacing A2:A10…
- Excel LEN Function Examples for Text, Spaces, and DataExcel’s LEN function returns the number of characters in a value, including spaces. For example, if A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.