How to Fix a VLOOKUP Exact-Match Error in Spreadsheets
Question details
The user needs to correct a VLOOKUP formula that returns unexpected values because it is defaulting to an approximate match rather than an exact match.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up text-based discount labels such as Low, Medium, and High using the VLOOKUP function.
- Observed behavior
- The VLOOKUP formula returns incorrect results when the optional fourth argument (range_lookup) is omitted.
Verify that your lookup values and the data in your table array do not contain accidental trailing spaces or typos, as an exact match requires the text strings to be completely identical.
Force an Exact Match Using the FALSE Argument
Update the VLOOKUP formula to explicitly require an exact match by adding FALSE as the fourth argument.
By default, when the fourth argument in a VLOOKUP function is omitted, the spreadsheet application assumes you want an approximate match (equivalent to TRUE). An approximate match requires the first column of your lookup table to be sorted in ascending alphabetical or numerical order.
If your data is not sorted, or if you are looking for specific text labels like 'Low', 'Medium', or 'High', relying on an approximate match will cause unexpected and incorrect results. You must explicitly tell the formula to find an exact match.
Click on the cell that contains the VLOOKUP formula producing the incorrect result.
Click into the formula bar at the top of your spreadsheet, or double-click the cell to enter edit mode.
Navigate to the end of the formula's arguments, just before the closing parenthesis. Type a comma followed by the word FALSE. For example, change =VLOOKUP(E470,VLUPTables!$A$2:$B$5,2) to =VLOOKUP(E470,VLUPTables!$A$2:$B$5,2,FALSE).
Press the Enter key on your keyboard. The formula will recalculate and should now return the exact match for your discount level.
Use WPS Spreadsheet for Accurate Data Lookups
WPS Spreadsheet provides a powerful, highly compatible environment for managing complex datasets. With built-in support for standard lookup functions like VLOOKUP, HLOOKUP, and XLOOKUP, you can analyze your data with precision and easily troubleshoot formula errors using intuitive formula evaluation tools.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your lookup tables.
- 2. Access the Function Wizard: Select the cell for your result, click the 'Formulas' tab on the top ribbon, and choose 'Insert Function'.
- 3. Configure VLOOKUP: Search for VLOOKUP. The wizard will provide clear input boxes for Lookup_value, Table_array, and Col_index_num.
- 4. Set Exact Match: In the Range_lookup box, simply type FALSE or 0 to guarantee an exact match, then click OK.
Frequently Asked Questions
Why does my VLOOKUP return a #N/A error when using FALSE?
A #N/A error indicates that the exact lookup value does not exist in the first column of your table array. When using FALSE, the match must be identical. Check for invisible trailing spaces, typos, or mismatched data types (such as numbers formatted as text).
Can I use 0 instead of FALSE in a VLOOKUP formula?
Yes. In spreadsheet logic, 0 is equivalent to FALSE, and 1 is equivalent to TRUE. Using =VLOOKUP(A2, B:C, 2, 0) is a faster way to write a formula that demands an exact match.
When is it appropriate to use TRUE (approximate match) in VLOOKUP?
TRUE is useful when looking up a value within a continuous numerical range, such as finding a tax rate based on income brackets or a tiered commission rate. For TRUE to work properly, the first column of your lookup table must be sorted in ascending order.
Is VLOOKUP case-sensitive when finding an exact match?
No, standard VLOOKUP is not case-sensitive. It treats 'Low' and 'low' as identical matches. If you need a strictly case-sensitive lookup, you will need to use a combination of the EXACT and INDEX/MATCH functions.
