How to Make VLOOKUP Return Exact Matches Only in Excel
Question details
The user needs the VLOOKUP function to stop returning approximate matches and only pull data when there is an exact match.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the VLOOKUP function to retrieve specific data from a table, but getting incorrect results because the function defaults to matching similar nearby values.
- Observed behavior
- VLOOKUP returns values for approximate matches, such as pulling data for 21855.5 when the actual lookup value is 21855.6.
Ensure that your lookup values and the data in your source table share the same formatting (e.g., both formatted as Text or Number) and contain no hidden trailing spaces, as exact matches will fail if the data types differ.
Set the Range Lookup Argument to FALSE
By explicitly setting the fourth argument of the VLOOKUP function to FALSE, you instruct Excel to only accept a 100% exact match.
By default, if you omit the fourth argument in a VLOOKUP formula, Excel assumes it is TRUE. A TRUE argument triggers an approximate match, which is useful for tax brackets or grading scales, but causes errors when looking up exact identifiers like employee IDs, product codes, or exact decimal values.
Double-click the cell containing your VLOOKUP formula, or click it once and place your cursor inside the formula bar at the top of the screen.
At the end of your formula, just before the closing parenthesis, add a comma followed by the word FALSE. For example, change =VLOOKUP(A2, Sheet2!A:D, 4) to =VLOOKUP(A2, Sheet2!A:D, 4, FALSE).
Press Enter to apply the change. If the exact value doesn't exist, Excel will now return a #N/A error instead of guessing. You can then drag the fill handle down to apply this exact-match formula to the rest of your column.
Use XLOOKUP for Default Exact Matching
Switching to the newer XLOOKUP function eliminates the need to specify exact match mode manually, as it defaults to exact matches.
Perform Exact VLOOKUPs Flawlessly in WPS Spreadsheet
WPS Office offers a powerful Spreadsheet application that fully supports standard Excel formulas including VLOOKUP, XLOOKUP, and HLOOKUP. It helps you manage large datasets and catch formula errors with an intuitive, free interface.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open your existing Excel workbook file.
- 2. Use the Function Wizard: Navigate to the Formulas tab on the ribbon and click on 'Insert Function'.
- 3. Search for VLOOKUP: Type VLOOKUP in the search bar, select it, and click OK to open the function arguments dialog.
- 4. Set Range_lookup to FALSE: Fill in your lookup value, table array, and column index. In the 'Range_lookup' box, simply type FALSE or 0 to guarantee an exact match, then click OK.
Frequently Asked Questions
Why does my exact match VLOOKUP return a #N/A error?
A #N/A error in exact-match mode means the lookup value does not exist in the source column, or there is a mismatch in data types. Check for invisible trailing spaces, or make sure numbers aren't stored as text in one of the columns.
What happens if I leave the fourth argument blank in VLOOKUP?
If you omit the fourth argument, VLOOKUP defaults to TRUE (approximate match). It will search for the closest match that is less than or equal to your lookup value, provided the lookup column is sorted in ascending order. If the column is not sorted, the result will be completely unpredictable.
Can I use 0 and 1 instead of FALSE and TRUE in VLOOKUP?
Yes. In Excel formulas, 0 is the exact equivalent of FALSE, and 1 is the exact equivalent of TRUE. Using =VLOOKUP(A1, B:C, 2, 0) is a very common shortcut used by professionals to enforce an exact match.
How do I fix trailing spaces that break my exact match VLOOKUP?
You can wrap your lookup value in the TRIM function to remove excess spaces. The formula would look like this: =VLOOKUP(TRIM(A2), Sheet2!A:D, 4, FALSE).
