excelisfun - Excel Partial Text Lookup Formulas. Top 5 methods for Fuzzy Lookup. Excel Magic Trick 1691.

Short Summary

Main recommendation: For partial text lookup (fuzzy lookup), use wildcards with XLOOKUP or VLOOKUP when the lookup value is a fragment of the full text, and use SEARCH combined with FILTER, XLOOKUP, or INDEX/MATCH when the lookup value is the full text and the match is a fragment inside it. The video demonstrates each approach in both Microsoft 365 Excel and older Excel versions.

Key Points

• Partial text lookup has two distinct situations: the lookup value is a fragment of the table text (e.g., "Coca Cola" matching "Coca Cola Inc."), or the lookup value is the full text and the table holds the fragment (e.g., "Coca Cola Inc." matching "Coca").
• When the lookup value is the fragment, wrap it in asterisks (*) on both sides, which match zero or more characters before and after.
• When the lookup value is the full text, SEARCH returns positions or errors that identify which table entries appear inside it.
• In Microsoft 365, spilled array formulas (XLOOKUP or FILTER) fill the whole column from one formula, avoiding manual copying.
• He recommends using IF with a shorter logical test instead of IFERROR or IFNA when a shorter test is available, because IFERROR and IFNA always evaluate the full formula, making them less efficient in that case.
• Top 5 methods for Fuzzy Lookup, as promised by the title, are not explicitly enumerated in the transcript; the video presents the following approaches instead:
  1. XLOOKUP with wildcards (Microsoft 365, fragment in lookup value)
  2. VLOOKUP with wildcards and IFNA/IFERROR (any version, fragment in lookup value)
  3. SEARCH with FILTER (Microsoft 365, full text in lookup value)
  4. XLOOKUP with SEARCH and TRUE (Microsoft 365, full text in lookup value)
  5. INDEX, MATCH, SEARCH, ISNUMBER, IF, and COUNT (any version, full text in lookup value)

Details Worth Noting

• Concrete instructions:
  • Case 1, fragment in lookup value (Microsoft 365): Use =XLOOKUP(""&A2&"", companies, cities, "not found", 0, ...). The wildcard pattern ""&cell&"" matches any text containing the fragment. Press F2 to edit, then supply all arguments so the result spills down the column.
  • Case 1, fragment in lookup value (older Excel): Use =VLOOKUP(""&A2&"", table, 2, FALSE), lock the table reference with F4, then wrap in IFNA (Excel 2013+) or IFERROR (Excel 2007+) with "not found" as the fallback.
  • Case 2, full text in lookup value (Microsoft 365): Use =FILTER(return_range, ISNUMBER(SEARCH(lookup_range, full_text)), "not found"). Lock the find-text range with F4 so it does not break the array operation.
  • Case 2, alternative (Microsoft 365): Use XLOOKUP(TRUE, ISNUMBER(SEARCH(lookup_range, full_text)), return_range, "not found").
  • Case 2, older Excel: Use =INDEX(return_range, MATCH(TRUE, ISNUMBER(SEARCH(lookup_range, full_text)), 0)) and wrap the logic in IF(COUNT(SEARCH(...)), INDEX/MATCH, "not found") to avoid the inefficiency of IFERROR/IFNA.
  • Use F9 on a selected portion of a formula to inspect the intermediate array of numbers and errors.
• The video notes that XLOOKUP's match_mode argument (wildcard mode) is what enables wildcard matching, and that a VLOOKUP or XLOOKUP approximate match is a related but different concept used for tax or commission rate tables.
• Using F4 to lock references is essential when copying or spilling formulas; the presenter advises selecting the entire range via the screen tip before pressing F4, since partial selection may lock only one part.
• The video says IFERROR and IFNA always run the full formula, so an IF with a shorter logical test is more efficient when one exists.

Bottom Line

Choose the method by checking which text is the fragment: wildcards handle a short lookup value inside a longer table entry, while SEARCH handles a long lookup value that contains a short table entry, and Microsoft 365 spilled arrays make both faster to apply.