# How to standardize inconsistent text entries with a lookup table in Excel

List each variant with its standard value, then look it up: =XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2).

**Excel:** `=XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Country (as entered) | Standard |  | Variant | Standard |
| USA |  |  | usa | United States |
| U.S. |  |  | u.s. | United States |
| United States |  |  | united states | United States |
| uk |  |  | uk | United Kingdom |
| Britain |  |  | britain | United Kingdom |
| Canada |  |  | canada | Canada |
| Mexico |  |  |  |  |

Formula in B2, filled to B8: `=XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2)`

Result: United States; United States; United States; United Kingdom; United Kingdom; Canada; Check: Mexico

## How it works

A mapping table turns many spellings into one value, and keeps the rules visible and easy to extend. XLOOKUP ignores case, so "USA" matches "usa". The fourth argument handles anything not in the table, and marking it "Check:" shows you which variants to add.

## Other ways

- `=IFERROR(VLOOKUP(TRIM(A2),$D$2:$E$7,2,FALSE),"Check: "&A2)`: The same lookup for versions without XLOOKUP.

## Common mistakes

- Variants with punctuation, like "U.S." and "US", are different keys. Add each one to the table.
- Forgetting the $ signs makes the table range slide down as you fill.

## Questions

**How do I list the entries missing from the table?**

Filter for the unmatched ones: `=UNIQUE(FILTER(A2:A8,ISNA(MATCH(TRIM(A2:A8),D2:D7,0))))`.

**Does this work in Google Sheets?**

Yes. Google Sheets has XLOOKUP too, and the VLOOKUP version works everywhere.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/standardize-text-entries-with-a-lookup-table