Function Problems

Fix VLOOKUP Returning Wrong Value or Cannot Find Data in Spreadsheets

Ayan Masood Oct 10, 2026 868 views

Question details

The user needs to troubleshoot a VLOOKUP formula that is returning incorrect results or failing to find specific lookup values, such as finishing positions in a racing points table.

How to Fix VLOOKUP Returning Wrong Values or Not Finding Positions

Product: Spreadsheets
Device & OS: not provided
Scenario: Looking up matching points for finishing positions from a data range where the formula returns errors, skips missing values, or returns inaccurate matches.
Observed behavior: The formula returns the wrong associated points for a finishing position or outputs an error indicating it cannot find a specific value, like the number 0.

Before you start

Ensure that your lookup values and your reference table data are formatted identically. Mismatches, such as numbers being stored as text in one column and as numerical values in the other, will cause lookup formulas to fail.

Solution 1Recommended

Enable Exact Match and Verify VLOOKUP Structure

Adjust the arguments in your VLOOKUP formula to ensure it performs an exact match and searches the correct primary column.

VLOOKUP defaults to an approximate match if the final argument is omitted, which often returns the wrong value when looking up unsorted numbers or specific text. Furthermore, VLOOKUP requires the lookup column to be the very first column on the left of your selected table array.

Place Lookup Data in the First Column

Check your reference table. The column containing the finishing positions (the value you are looking up) must be the leftmost column of the range you select for the 'table_array' argument.

Count Your Columns Carefully

Verify the 'col_index_num' argument. This is the column number within your selected table array that contains the points you want to return. If your table spans A to C and points are in C, the number is 3.

Add the Exact Match Parameter

Add 'FALSE' or '0' as the final argument in your formula. Your final formula should look similar to: =VLOOKUP(A2, D2:E20, 2, FALSE).

Prevent Formula Shifting: When dragging your formula down to other rows, lock your table array references by pressing F4 (e.g., $D$2:$E$20) to prevent the search range from sliding downward and missing top-level data.

Solution 2

Use XLOOKUP Instead of VLOOKUP

Switch to XLOOKUP to bypass VLOOKUP's strict layout limitations and default approximate match settings.

Solution 3

Resolve Data Type Inconsistencies

Fix numbers stored as text that prevent VLOOKUP from recognizing identical values.

Advanced Spreadsheet Features

Master Formulas Easily with WPS Spreadsheet

WPS Spreadsheet provides an intuitive environment for managing complex data. With built-in syntax prompts and highly compatible modern formulas like XLOOKUP, you can easily pull data without frustrating errors.

1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your racing points data.
2. Access the Formula Tab: Navigate to the 'Formulas' tab on the ribbon and click 'Insert Function'.
3. Search for Lookup Functions: Type 'VLOOKUP' or 'XLOOKUP' in the search bar and select the function. The wizard will guide you to select your lookup value, array, and return range without typing commas or brackets manually.

Fully compatible with Microsoft Excel formulas, ensuring your VLOOKUP and XLOOKUP functions work seamlessly.Intuitive Formula Builder with step-by-step argument input to prevent syntax errors.Smart data cleanup tools to instantly fix text-to-number format mismatches.Lightweight, fast, and completely free for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return #N/A even when the value exists?

The #N/A error usually means the exact lookup value cannot be found in the first column of your table array. This happens due to trailing spaces, numbers formatted as text, or because you forgot to lock the table array with absolute references (like $A$1:$B$10) before dragging the formula down.

Can VLOOKUP look up data to the left of the search column?

No, VLOOKUP can only search for the lookup value in the leftmost column of the specified table array and return values to the right. To look up values to the left, you must use XLOOKUP or an INDEX and MATCH combination.

What does TRUE and FALSE mean at the end of a VLOOKUP?

TRUE (or 1) tells VLOOKUP to find an approximate match, which is useful for categorizing numbers like tax brackets, provided the data is sorted in ascending order. FALSE (or 0) forces VLOOKUP to find an exact match, which is highly recommended for looking up specific IDs, names, or individual points.

Why is VLOOKUP giving me the result from the row above?

This happens when your formula is set to an approximate match (omitting the final argument or using TRUE) and the data is not sorted in ascending order. Adding FALSE to the end of your formula will force it to find the exact row.

Excel & Data Problems