Function Problems

Fix Incorrect LOOKUP and Approximate VLOOKUP Results in Spreadsheets

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

The user needs to correct LOOKUP and approximate-match VLOOKUP results when lookup values are not sorted.

Fix Incorrect LOOKUP and Approximate VLOOKUP Results in Spreadsheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the full table

Select the lookup column and its associated result columns together so each row stays intact during sorting.

2
Sort the lookup values

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.

3
Check the result

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.

Sort the Lookup Range for Approximate Matches
Keep corresponding values together: For LOOKUP with separate lookup and result vectors, sort the associated rows together so each result remains paired with its lookup value.
Work with lookup formulas

Correct Lookup Results in WPS Spreadsheets

Use WPS Spreadsheets to sort lookup tables for approximate matches or change VLOOKUP to an exact match.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select the workbook containing your lookup formula.
  2. 2. Select the lookup table: Highlight the lookup column and its related result columns together.
  3. 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. 4. Verify the result: Select the formula cell and check that the returned value matches the lookup data.
Sort lookup values and their associated rows togetherUse LOOKUP and VLOOKUP formulas in spreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.