XLOOKUP with wildcards

1 / 5

The orders only say Riverside, but the list has the full name, Riverside Limited. An exact lookup finds nothing.

keyboard Typing formulas is easiest on a computer with a keyboard.

Riverside

Riverside Limited

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How XLOOKUP works

What are wildcards?

Characters that stand for other characters when matching text:
- * stands for any number of characters, even none. "Harbour*" matches Harbour Ltd and Harbour Foods.
- ? stands for exactly one character. "Gr?y" matches Grey and Gray.
- ~ before * or ? means a real asterisk or question mark.
How do you use them in XLOOKUP?

Switch them on with 2 as the fifth part, match_mode. Without it, XLOOKUP looks for a real asterisk:
=XLOOKUP(E2&"*",$A$2:$A$11,$B$2:$B$11,,2)
E2&"*" means "starts with what's in E2". "*"&E2&"*" means "contains it anywhere".
Do other functions use them?

Yes, and there they are always on: VLOOKUP and MATCH with an exact match, COUNTIF, SUMIF and the other IFS functions, and SEARCH. =COUNTIF(A2:A11,"*Ltd") counts the names ending in Ltd.
What's the catch?

A wildcard returns the first match. If two names start with "Harbour", you get whichever comes first, so make the pattern specific enough.

Know someone fighting with a spreadsheet? Send them this page.