Function Problems

How to Fix XLOOKUP Returning 'Not Found' Across Excel Worksheets

Natalie Taylor Sep 28, 2026 869 views

Question details

The user needs to retrieve data from a different worksheet using XLOOKUP, but the formula keeps returning a 'Not Found' error despite matching values seemingly existing in both sheets.

How to Fix XLOOKUP Returning 'Not Found' Across Excel Worksheets

Product: Excel
Device & OS: not provided
Scenario: Using the XLOOKUP function to match subdivision names across multiple worksheets to pull corresponding percentage values from a specific column.
Observed behavior: The formula fails to find a match and returns the custom 'Not Found' text, likely due to subtle formatting discrepancies, trailing spaces, or shifting cell references.

Before you start

Verify that your worksheet names are spelled exactly as they appear in the sheet tabs, and ensure your workbook's calculation option is set to 'Automatic' so formulas update instantly.

Solution 1Recommended

Use Absolute References and Format Verification

Lock your array references so they don't shift when copying the formula down, and ensure both columns use identical data types (e.g., text vs. percentages).

When copying a formula down a column, Excel automatically shifts relative cell references. If your lookup array is not locked, the search area will move down, causing valid matches to be skipped.

Lock the lookup ranges

Modify the array references in your formula from relative (e.g., Neigh_Data!C5:C10) to absolute (e.g., Neigh_Data!C$5:C$10) by pressing F4 or manually typing the dollar signs.

Check formatting differences

Select the source and destination columns, right-click, and choose 'Format Cells'. Ensure both are set to the same format (e.g., both General, or both Text) so Excel doesn't misinterpret numbers as text.

Apply the corrected formula

Enter the formula: =XLOOKUP(B5, Neigh_Data!C$5:C$10, Neigh_Data!F$5:F$10, "Not Found") into your target cell and press Enter.

Fill the formula down

Click the small square at the bottom-right corner of the cell containing your new formula and drag it down to apply it to the rest of the column.

Use Absolute References and Format Verification

Data Type Consistency: If you are looking up a percentage (like 2.35%), make sure it isn't formatted as a text string in one sheet and a true decimal (0.0235) in the other.

Solution 2

Clean Data with TRIM and CLEAN Functions

Use text-cleaning functions to remove hidden spaces or non-printable characters that make visually identical text fail an exact match in XLOOKUP.

Efficient Data Analysis with WPS Office

Use XLOOKUP Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers full native support for advanced functions like XLOOKUP, TRIM, and CLEAN. It provides a lightweight, fast environment for managing complex data lookups across multiple worksheets without lag or errors.

1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx workbook containing your subdivision data.
2. Insert the XLOOKUP function: Select the target cell, go to the Formula tab, click 'Insert Function', and search for XLOOKUP.
3. Select your arrays: Use your mouse to easily select the lookup value, switch to the secondary worksheet tab, and highlight your absolute lookup and return arrays.
4. Apply and drag: Press Enter to generate the result, then drag the fill handle down to populate the entire column seamlessly.

Fully compatible with Microsoft Excel formulas and .xlsx files.Built-in formula error tracing to easily spot broken references.Fast processing speeds when querying large datasets across multiple sheets.Completely free to use with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return an error when the text looks exactly the same?

This is almost always caused by hidden formatting issues. Trailing spaces, leading spaces, or invisible non-printable characters make strings technically different. Using the TRIM or CLEAN function helps normalize the data so XLOOKUP can find the exact match.

Can XLOOKUP pull data from a completely different, closed workbook?

Yes, XLOOKUP can reference arrays in a separate workbook. However, when referencing a closed workbook, you must include the full file path enclosed in single quotes within the formula.

What does the 'Not Found' argument do in XLOOKUP?

The 'Not Found' argument is the optional fourth parameter in the XLOOKUP syntax. It allows you to specify custom text (like "Not Found" or "Missing") or a value (like 0) to display instead of the default #N/A error when a match cannot be found.

Do I need to sort my data for XLOOKUP to work accurately?

No. Unlike VLOOKUP (when set to approximate match), XLOOKUP defaults to an exact match and will search from the top down by default, regardless of whether your source data is sorted.

Excel & Data Problems