How to standardize inconsistent text entries with a lookup table in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2)List each variant with its standard value, then look it up: =XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2).
- Result on the example
- one result per row in
B2, filled down toB8 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B8. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2)Result"United States"
A2"USA"The value in A2.
TRIM(A2)"USA"TRIM: Removes extra spaces, leaving single spaces between words.- text
A2"USA"
- text
$D$2:$D$76 rows × 1 col: "usa", "u.s.", "united states", "uk", …A range: 6 cells in one column.
$E$2:$E$76 rows × 1 col: "United States", "United States", "United States", "United Kingdom", …A range: 6 cells in one column.
"Check: "&A2"Check: USA"The & operator joins as text.
XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2)"United States"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRIM(A2)"USA" - lookup_array
$D$2:$D$76 rows × 1 col: "usa", "u.s.", "united states", "uk", … - return_array
$E$2:$E$76 rows × 1 col: "United States", "United States", "United States", "United Kingdom", … - if_not_found
"Check: "&A2"Check: USA"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFERROR(VLOOKUP(TRIM(A2),$D$2:$E$7,2,FALSE),"Check: "&A2) | United States, United States, United States… | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Standardize inconsistent text entries with a lookup table
B2, replace the entry with its standard name from the table in D2:E7, or show "Check: " and the entry when it isn't in the table. Fill down to B8.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.