Excel XLOOKUP Trim: How to Remove Spaces

Formulas & functions · Updated November 22, 2026

To trim spaces inside an XLOOKUP, wrap the lookup value and the lookup array in the TRIM function: =XLOOKUP(TRIM(A2), TRIM(B:B), C:C). This removes leading, trailing, and repeated internal spaces from both the value you’re searching for and the values in the lookup column, so matches succeed even when the data has inconsistent spacing.

Why Trimming Matters in XLOOKUP

Excel’s XLOOKUP performs exact matches by default. If the lookup value in cell A2 contains a stray space (e.g., “Apple ” instead of “Apple”) or the lookup column has extra spaces (e.g., “Apple” with a trailing space), XLOOKUP returns #N/A even though the data looks identical. This is a common issue when data is imported from other systems, copied from web pages, or manually entered with inconsistent spacing. The TRIM function removes all spaces except single spaces between words, making your lookup resilient to these formatting quirks.

How to Use TRIM Inside XLOOKUP

Assume you have a product list with names in column A (A2:A100) and prices in column B (B2:B100). Your lookup value is in cell D2. The formula to trim both sides is:

=XLOOKUP(TRIM(D2), TRIM(A2:A100), B2:B100)

This works because TRIM is applied to the entire array A2:A100, returning an array of trimmed values that XLOOKUP searches. The result is the price from column B. If you need to handle case-insensitive matching, combine with UPPER or LOWER: =XLOOKUP(TRIM(UPPER(D2)), TRIM(UPPER(A2:A100)), B2:B100).

Using TRIM with Wildcards in XLOOKUP

XLOOKUP supports wildcard characters (*, ?, ~) when the match mode is set to 2 (wildcard). If you want to find a value that contains extra spaces anywhere, you can combine TRIM with wildcards. For example, to find a product name that might have spaces in the middle, use:

=XLOOKUP("*"&TRIM(D2)&"*", TRIM(A2:A100), B2:B100, , 2)

Here, the TRIM(D2) cleans the lookup value, and the wildcards allow partial matches. The fifth argument (2) enables wildcard matching. Note that TRIM on the lookup array still helps because wildcard matching is case-insensitive by default.

Common Problems and Solutions

  • #N/A error after trimming: If trimming both sides still returns #N/A, the issue may be non-breaking spaces (character code 160) that TRIM doesn’t remove. Use SUBSTITUTE to replace them: =XLOOKUP(TRIM(SUBSTITUTE(D2, CHAR(160), " ")), TRIM(SUBSTITUTE(A2:A100, CHAR(160), " ")), B2:B100).
  • Array formula required? In older Excel versions, XLOOKUP with TRIM on an array might need to be entered with Ctrl+Shift+Enter. In Excel 2021 and Microsoft 365, it works normally as a dynamic array formula.
  • Performance: Applying TRIM to a large array (e.g., 100,000 rows) can slow down calculation. Consider cleaning the data once in a helper column instead of trimming on every lookup.

Alternatives to TRIM in XLOOKUP

If you cannot modify the formula or the data, you can use CLEAN to remove non-printable characters, or SUBSTITUTE to replace specific characters. For example, to remove all spaces (not just extra ones), use =XLOOKUP(SUBSTITUTE(D2, " ", ""), SUBSTITUTE(A2:A100, " ", ""), B2:B100). This is useful when you want to ignore spaces entirely, but be careful—it will also remove spaces between words, so “New York” becomes “NewYork”. For most cases, TRIM is safer.

Best Practices for Clean Lookups

  • Clean your data before using XLOOKUP: use TRIM in a helper column, then reference that column in your lookup.
  • Use Excel’s Text to Columns (Data tab) or Flash Fill to remove spaces quickly.
  • Consider using TRIM in combination with PROPER or UPPER for consistent formatting.
  • If you’re working with imported data, use Power Query to trim all columns during import—this avoids repeated formula overhead.

Frequently Asked Questions

Does TRIM remove all spaces?

No, TRIM removes leading, trailing, and repeated internal spaces, but it leaves single spaces between words. For example, ” New York ” becomes “New York”. To remove all spaces, use SUBSTITUTE as shown above.

Why does XLOOKUP still return #N/A after trimming?

Possible reasons: non-breaking spaces (CHAR(160)), hidden characters, or the lookup value is a number stored as text. Check with =LEN(A2) to see the actual length. Use CLEAN to remove non-printable characters, and ensure data types match (e.g., convert text to numbers with VALUE).

Can I use TRIM with XLOOKUP in Excel for Mac?

Yes, the formula syntax is identical on Mac. The only difference is keyboard shortcuts for entering formulas, but the functions work the same.

Is there a way to trim only the lookup array, not the lookup value?

Yes, simply omit TRIM on the lookup value: =XLOOKUP(D2, TRIM(A2:A100), B2:B100). This is useful when you’re sure the lookup value is clean but the data column isn’t.

Does XLOOKUP support wildcards with TRIM?

Yes, as shown earlier, you can combine TRIM with wildcard match mode (match mode 2). Just remember to set the fifth argument to 2.

What if I need to trim multiple columns for a two-column lookup?

If you’re using XLOOKUP with a return array that spans multiple columns, you can trim each column separately in the return array. For example, =XLOOKUP(TRIM(D2), TRIM(A2:A100), TRIM(B2:C100))—but note that TRIM on a multi-column array returns an array of the same shape, so it works.

Final Thoughts

Trimming in XLOOKUP is a simple yet powerful technique to avoid common data entry errors. By wrapping the lookup value and lookup array with TRIM, you ensure that extra spaces don’t break your lookups. For persistent issues, clean your source data upfront or use SUBSTITUTE for special space characters. With these methods, you’ll get accurate matches every time.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.