How to VLOOKUP with a partial match (contains)
✓ Verified in LibreOffice 25.8.7.3Find a row when you only know part of the key — a fragment of a name, code, or title.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =VLOOKUP("*"&D2&"*",A2:B10,2,FALSE) | Wildcards work in exact-match mode. XLOOKUP needs match_mode 2 for wildcards. |
| Google Sheets | =VLOOKUP("*"&D2&"*",A2:B10,2,FALSE) | Identical. |
| LibreOffice Calc | =VLOOKUP("*"&D2&"*",A2:B10,2,FALSE) | Identical (wildcards must be enabled in Calc options — they are by default for xlsx files). |
How it works
Wrapping the fragment in asterisks turns it into a contains-pattern: "*Turing*" matches "Alan Turing" and returns 1912. The wildcards only fire in EXACT-match mode (FALSE) — the mode people think of as the strict one. First match wins when several rows contain the fragment; ~ escapes a literal * or ? in the data. In XLOOKUP the same needs match_mode 2, and case never matters.
Verified, not just documented
We ran =VLOOKUP("*"&D2&"*",A2:B4,2,FALSE) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 1912 — exactly the expected result. Every formula here is confirmed by actually executing it.