Function Problems

Fix XLOOKUP Not Working Due to MID Function Text Results in Excel

Aamir Naveed Akram Sep 30, 2026 870 views

Question details

XLOOKUP returns a zero or fails to find a match because the MID function extracts digits as text strings, creating a data type mismatch with numerical lookup values.

Fix XLOOKUP Returning Zero When MID Function Results Are Text

Product: Spreadsheet
Device & OS: not provided
Scenario: Using XLOOKUP to match numeric values against data extracted via the MID function, often involving pivot tables where the extracted data behaves as text.
Observed behavior: The XLOOKUP formula returns zero or an error. Formatting the cell directly to 'Number' does not resolve the problem, as the underlying extracted data remains formatted as text.

Before you start

Verify that your lookup value is a number while your source array is generated by text functions. You can test this by applying the =ISNUMBER() function to the cell containing your MID formula to see if it returns FALSE.

Solution 1Recommended

Use the VALUE Function to Convert Text to Numbers

Wrapping your MID function in a VALUE function forces the spreadsheet to treat the extracted text digits as actual numerical values, fixing the lookup mismatch.

Text functions like MID, LEFT, and RIGHT inherently output text strings, even if the result only contains digits. Because lookup functions require exact data type matches, looking up a number against a text string will fail. The VALUE function resolves this by converting recognized text formats into standard numbers.

Locate the extraction formula

Click on the cell containing your original MID formula (for example, =MID($B2,6,4)).

Wrap the formula with VALUE

Modify the formula in the formula bar to include the VALUE function. It should look like this: =VALUE(MID($B2,6,4)).

Apply the changes

Press Enter to save the formula, then drag the fill handle down to apply this updated formula across your data range.

Refresh Pivot Tables

If this data is linked to a Pivot Table, right-click the table and select 'Refresh'. Your XLOOKUP will now match the numbers correctly.

Use the VALUE Function to Convert Text to Numbers

Data Type Matched: Changing the formula ensures the underlying data type is numeric. Unlike simply changing the cell format, this method guarantees that lookup functions process the data correctly.

Solution 2

Use the Double Negative (--) Operator

Use a double hyphen before the MID function as a quicker mathematical alternative to convert text numbers into actual numbers.

Effortless Data Processing

Resolve Formula Errors Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides a robust environment for managing complex datasets. With full support for advanced lookup functions and text parsing, you can quickly fix data type mismatches and ensure your formulas run flawlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your pivot table and XLOOKUP formula.
2. Locate the MID Formula: Find the column or cell where you are extracting data using the MID function.
3. Wrap with VALUE: Edit the formula bar to wrap your existing formula inside the VALUE function, e.g., =VALUE(MID(B2,6,4)).
4. Apply and Refresh: Press Enter, drag the fill handle to apply the formula to the entire column, and refresh any associated pivot tables to fix the XLOOKUP mismatch.

Fully compatible with Microsoft Office Excel formulas, including XLOOKUP and VALUE.Advanced error-checking features to quickly identify and resolve data type mismatches.Lightweight architecture ensures fast calculations, even with massive pivot tables.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't changing the cell format to 'Number' fix my XLOOKUP error?

Changing the cell format from the formatting ribbon only changes the visual display of the data, not its underlying computational data type. Since the MID function inherently outputs text, you must use a function like VALUE or a mathematical operation to change the actual data structure for XLOOKUP to recognize it as a number.

Does the double negative (--) work the same as the VALUE function?

Yes, placing a double minus sign before a text-extraction function mathematically coerces the text string into a number. It is a slightly faster, albeit less readable, alternative to using the VALUE function. Both methods achieve the exact same data type conversion.

Will this fix apply to VLOOKUP and INDEX/MATCH functions as well?

Absolutely. Any lookup function in a spreadsheet, including VLOOKUP and INDEX/MATCH, requires exact data type matches between the lookup value and the lookup array. Converting your text digits to numerical values will resolve data mismatch errors across all these lookup formulas.

Can I convert the XLOOKUP criteria instead of changing the MID result?

Yes. If you prefer to leave the MID results as text, you can convert your numeric lookup value into text directly inside the XLOOKUP function by concatenating it with an empty string. For example: =XLOOKUP(A2&"", Lookup_Array, Return_Array).

Excel & Data Problems