Fix Incorrect LOOKUP and Approximate VLOOKUP Results in Spreadsheets
Question details
The user needs to correct LOOKUP and approximate-match VLOOKUP results when lookup values are not sorted.

- Product
- Spreadsheet software, including WPS Spreadsheets and Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using LOOKUP or VLOOKUP to find a value in a spreadsheet range.
- Observed behavior
- The formula can return an incorrect result when the lookup range is not sorted in ascending order.
Identify the lookup range in your formula and check whether its lookup values are in ascending order. For VLOOKUP, the lookup column must be the first column of the selected table range.
Sort the Lookup Range for Approximate Matches
Use this method when LOOKUP or approximate-match VLOOKUP should return a value based on a sorted lookup range.
VLOOKUP uses an approximate match when its fourth argument is TRUE or omitted. LOOKUP also requires its lookup vector to be sorted in ascending order.
Select the lookup column and its associated result columns together so each row stays intact during sorting.
Open Data > Sort, select the first column of the VLOOKUP range or the LOOKUP vector, and choose ascending order. Specify whether the selection has headers if prompted.
Return to the formula cell and check the result. For example, =VLOOKUP(E2,A2:B10,2,TRUE) searches the sorted first column for an approximate match.

Use Exact-Match VLOOKUP Instead
Use this method when you need an exact match rather than the nearest lower value.
Correct Lookup Results in WPS Spreadsheets
Use WPS Spreadsheets to sort lookup tables for approximate matches or change VLOOKUP to an exact match.
- 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select the workbook containing your lookup formula.
- 2. Select the lookup table: Highlight the lookup column and its related result columns together.
- 3. Sort or change the formula: For an approximate match, choose Data > Sort and sort the lookup column in ascending order. For an exact VLOOKUP match, edit the formula and set its fourth argument to FALSE.
- 4. Verify the result: Select the formula cell and check that the returned value matches the lookup data.

Frequently Asked Questions
Why does VLOOKUP return the wrong value when I omit the last argument?
VLOOKUP defaults to an approximate match when the fourth argument is omitted. Sort the first column of the table range in ascending order, or add FALSE as the fourth argument for an exact match.
Does exact-match VLOOKUP need a sorted column?
No. A formula such as =VLOOKUP(E2,A2:B10,2,FALSE) can search an unsorted first column. It returns #N/A if it cannot find an exact match.
How should I sort a LOOKUP vector without mismatching results?
Select the lookup vector and its associated result values together, then use Data > Sort to sort the lookup values in ascending order. This keeps each result aligned with its lookup value.
What does approximate-match VLOOKUP return if there is no exact match?
When the first column is sorted in ascending order, it returns the result associated with the largest lookup value less than or equal to the search value. If the search value is smaller than the first value, it returns #N/A.




