Flexible Lookups in Excel: Exact Match, Starts With, Contains, and Beyond

When people think of lookups in Excel, the first formula that comes to mind is usually VLOOKUP. But real-world data rarely fits into neat, exact-match situations. Often, you need to look up values based on partial matches, patterns, or more complex logic such as “starts with,” “ends with,” or “contains.”

In this guide, we’ll dive deep into every possible way you can perform advanced lookups in Excel using built-in formulas; from classic approaches like wildcards in VLOOKUP to modern solutions with XLOOKUP, FILTER, and SEARCH.

Comparison of Lookup Methods

Different formulas can achieve similar results, but each comes with trade-offs. Here’s a comparison:

Pro Tip: If you want your file to be backward-compatible for older Excel users, avoid XLOOKUP and FILTER. Stick with INDEX/MATCH or VLOOKUP.

The Classic Exact-Match Lookup

The most straightforward type of lookup is finding an exact match.

Using VLOOKUP

=VLOOKUP(“Apple”, A2:B100, 2, FALSE)

• Looks for “Apple” in column A.
• Returns the corresponding value from column B.
• The final argument FALSE ensures exact match only.

Using XLOOKUP

=XLOOKUP(“Apple”, A2:A100, B2:B100, “Not Found”)

• More flexible than VLOOKUP.
• Does not require column indexes.
• Provides a default value if nothing is found.

Lookups with Partial Matches

Sometimes you don’t know the full text. That’s where wildcards come in.

Wildcard Deep Dive: *, ?, and Escaping with ~

Excel supports two main wildcard characters (well, actually three) inside lookup functions (XLOOKUP, VLOOKUP, MATCH, etc.):

• Asterisk (*) – matches any number of characters.
  Example:
  =XLOOKUP(“App*”, A2:A100, B2:B100, “Not Found”, 0)
  → Returns the first match that starts with “App” (e.g., Apple, Application, Appointment).
• Question Mark (?) – matches a single character only.
  Example:
  =XLOOKUP(“Jo?n*”, A2:A100, B2:B100, “Not Found”, 0)
  → Matches “Johns” and “Joanette” but not “Jonathan” (because the ? replaces exactly one character).
• Tilde (~) Escape – use when you want to find a literal * or ? character in text.
  Example:
  =XLOOKUP(“File~*”, A2:A100, B2:B100, “Not Found”, 0)
  → Finds the text “File*” instead of interpreting * as a wildcard.

Pro Tip: Wildcards only work with match_mode = 0 in XLOOKUP (exact match). If you leave match_mode blank or use 1/-1, wildcards won’t be recognized.

Starts With

If you want to match any value that starts with “App”:

=XLOOKUP(“App*”, A2:A100, B2:B100, “Not Found”, 0)

Here, “App*” means: any text starting with “App”.

Ends With

To match values that end with “ple”:

=XLOOKUP(“*ple”, A2:A100, B2:B100, “Not Found”, 0)

Contains

To find text that contains “pp” anywhere:

=XLOOKUP(“*pp*”, A2:A100, B2:B100, “Not Found”, 0)

Performance Considerations

Wildcards are powerful but can slow down large workbooks. Key things to watch:

• Processing Cost: Every * or ? forces Excel to scan every character in the search range. On 100k+ rows, this can be noticeably slow.
• Helper Columns Help: Instead of searching directly with wildcards, create helper columns with LEFT, RIGHT, MID, or SEARCH results. Then run lookups on those smaller results.
  Example:
  • Helper Column: =LEFT(A2,3) → Extracts the first 3 letters.
  • Lookup: =XLOOKUP(“App”, E2:E100, B2:B100) → Much faster than * wildcards.
• Tables and Named Ranges: Using Excel Tables (Ctrl+T) helps keep lookups dynamic without scanning unnecessary empty cells.
• Avoid Full Column References: Although A:A is convenient, it makes Excel check over a million rows. Use A2:A1000 unless you’re certain performance won’t be affected.

Case-Sensitive Lookups

By default, Excel lookups ignore case. To make them case-sensitive, you need a formula hack.

=INDEX(B2:B100, MATCH(TRUE, EXACT(“Apple”, A2:A100), 0))

• EXACT compares with case sensitivity.
• MATCH(TRUE, …) finds the position where the case-sensitive match occurs.
• INDEX retrieves the result.

Lookups Using SEARCH or FIND

If you need more dynamic control (e.g., look up if a word contains part of another word), you can use SEARCH (case-insensitive) or FIND (case-sensitive).

Example: Lookup When a Cell Contains “App”

=INDEX(B2:B100, MATCH(TRUE, ISNUMBER(SEARCH(“App”, A2:A100)), 0))

• SEARCH(“App”, A2:A100) returns a number if “App” is found.
• ISNUMBER converts that into TRUE/FALSE.
• MATCH locates the first TRUE.
• INDEX returns the value.

This technique is powerful for “contains” searches when wildcards are not enough.

Multi-Criteria Lookups

Sometimes you need to look up based on more than one condition; for example, Product and Region.

Using INDEX + MATCH

=INDEX(C2:C100, MATCH(1, (A2:A100=”Apple”) * (B2:B100=”East”), 0))

(Enter as an array formula in older Excel, or just press Enter in modern Excel.)

• A2:A100=”Apple” and B2:B100=”East” both return TRUE/FALSE arrays.
• Multiplying them forces both to be TRUE (like an AND condition).
• MATCH(1, …) finds the row where both are TRUE.
• INDEX retrieves the result.

Lookups That Return Multiple Results

Modern Excel (Office 365 / Excel 2021+) allows spill formulas that return more than one result at once.

Example: FILTER

=FILTER(B2:B100, ISNUMBER(SEARCH(“App”, A2:A100)), “Not Found”)

• Returns all values where column A contains “App.”
• Much more powerful than traditional single-result lookups.

Numeric and Approximate Lookups

Text isn’t the only scenario. You can also perform range lookups.

Example: Grade Lookup

=VLOOKUP(85, A2:B6, 2, TRUE)

If your table is:

• 0 → F
• 60 → D
• 70 → C
• 80 → B
• 90 → A

An input of 85 returns B.

With XLOOKUP, the equivalent is:

=XLOOKUP(85, A2:A6, B2:B6, “Not Found”, 1)

Dynamic Array + LOOKUP Functions

Dynamic arrays open the door for new, simplified formulas.

Example: Return All Matches That Start With “App”

=FILTER(B2:B100, LEFT(A2:A100,3)=”App”, “Not Found”)

Here:

• LEFT(A2:A100,3) extracts the first 3 characters.
• Compares to “App”.
• FILTER spills all corresponding matches.

Advanced Patterns: Regex in Excel (Microsoft 365 Insider / VBA)

For years Excel users made do with * and ? wildcards, SEARCH/FIND, VBA or Power Query hacks. Now Excel finally gives you true regex inside formulas, which dramatically expands what you can match, extract, validate and replace inside worksheets. In short: regex lets you describe patterns (digits, word boundaries, optional groups, lookarounds, repetition counts, etc.) instead of relying on crude wildcards or long nested MID/LEFT expressions. Native Excel regex functions (REGEXTEST, REGEXEXTRACT, REGEXREPLACE) are available in Microsoft 365.

What the new functions do (quick overview + syntax)

• REGEXTEST(text, pattern, [case_sensitivity]) → returns TRUE/FALSE (useful for FILTER, conditional formatting, IF tests).
• REGEXEXTRACT(text, pattern, [occurrence], [case_sensitivity]) → extracts the first (or nth) match; great for grabbing IDs, phone numbers, emails.
• REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) → replace with support for capture groups and $1 style backreferences.
  Microsoft’s implementation accepts optional case_sensitivity flags and follows PCRE2 so you can use \d, \w, lookaheads, quantifiers and more.

Practical, copy-and-paste examples

(Assume column A has messy text.)

• Extract the first number sequence (product ID): =REGEXEXTRACT(A2, “\d+”)
• Extract phone numbers like 123-456-7890: =REGEXEXTRACT(A2, “\d{3}-\d{3}-\d{4}”)
• Return TRUE if a cell contains an email address (quick validation): =REGEXTEST(A2, “[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}”)
• Normalize whitespace (replace runs of whitespace with a single space): =REGEXREPLACE(A2, “\s+”, ” “)
• Swap FirstNameLastName → LastName, FirstName using capture groups: =REGEXREPLACE(A2, “([A-Z][a-z]+)([A-Z][a-z]+)”, “$2, $1”)

Using regex inside lookups, FILTER and XLOOKUP

Regex plays nicely with the other dynamic functions:

• List all rows where Notes contain the word “urgent”: =FILTER(Table[Value], REGEXTEST(Table[Notes], “\burgent\b”), “No matches”)
• Return the first match’s return value (INDEX + MATCH style): =INDEX(B2:B100, MATCH(TRUE, REGEXTEST(A2:A100, “INV-\d{6}”), 0))

Debugging & best practices for regex in Excel

• Test patterns first with REGEXTEST (fast TRUE/FALSE checks) before using REGEXEXTRACT/REGEXREPLACE across a sheet.
• Use case_sensitivity parameter when you need exact case matching (the functions accept a flag to toggle case).
• Avoid overly-greedy patterns on very large ranges; complex or catastrophic patterns can slow calculation; when in doubt, benchmark on a sample.
• Leverage $1, $2 in REGEXREPLACE to reorder or reformat extracted pieces without helpers (very powerful for name reformatting, phone normalization, etc.).

Example workflow I recommend

1. Build and test your pattern on a regex tester (regex101 / regexr) to confirm groups and edge cases.
2. Use REGEXTEST in Excel to quickly mark rows that match.
3. Swap to REGEXEXTRACT/REGEXREPLACE to extract/clean data.
4. Wrap the results in FILTER/XLOOKUP/INDEX to perform lookups off the cleaned values.

Short note on safety & performance

Regex is powerful but can be complex. Avoid unnecessarily complicated constructs on huge tables without testing (catastrophic backtracking is possible with certain patterns). If performance matters, prefer Power Query or helper columns to precompute and then use lookups on those helper results.

Best Practices for Lookups in Excel

• Prefer XLOOKUP over VLOOKUP: cleaner syntax, fewer errors.
• Use wildcards for flexible matching: especially “*” for starts/ends/contains.
• Combine with SEARCH/FIND for custom rules.
• Use FILTER for multiple results instead of complex array INDEX/MATCH.
• Handle errors gracefully with IFERROR or the default argument in XLOOKUP.
• Keep ranges structured: consider Excel Tables to avoid broken references.

Quick Reference: Excel Lookup Types and Formulas

By now we’ve covered a wide range of lookup techniques, from simple exact matches to advanced wildcard searches and multi-criteria formulas. To bring everything together, the table below distills all of these methods into a single reference you can return to whenever you need a refresher.

Troubleshooting Lookup Methods in Excel

Even the best formulas can break down when faced with messy data, wrong settings, or unexpected edge cases. Below is a comprehensive troubleshooting guide that covers every lookup method we discussed in this article.

Troubleshooting XLOOKUP

Common Issues:

• Wildcards not working → You forgot to set match_mode = 0. By default, wildcards only work in exact match mode.
• Case sensitivity confusion → XLOOKUP is not case-sensitive. To force case sensitivity, wrap your search term in EXACT.
• Performance lag → Avoid using entire column references like A:A. Use defined ranges or tables.
• Wrong return value → Remember XLOOKUP only returns the first match. If you need multiple matches, switch to FILTER.

Fixes:

• Add 0 as the match mode:
  =XLOOKUP(“App*”, A2:A100, B2:B100, “Not Found”, 0)
• If performance drops, use helper columns (LEFT, RIGHT, SEARCH).
• For multiple matches:
  =FILTER(B2:B100, ISNUMBER(SEARCH(“App”, A2:A100)), “Not Found”)

Troubleshooting VLOOKUP

Common Issues:

• Left Lookup not possible → VLOOKUP only searches rightward.
• Column index number wrong → If you insert or delete columns, your formula breaks.
• Wildcard not matching → Ensure you typed it correctly and avoided trailing spaces.
• Approximate match errors → If you forget the last argument (TRUE/FALSE), Excel defaults to approximate matching.

Fixes:

• Replace VLOOKUP with INDEX + MATCH for left lookups.
• Use structured references or INDEX instead of hardcoding column numbers.
• Always set the 4th argument:
  • Exact match → =VLOOKUP(“App*”, A2:B100, 2, FALSE)
  • Approx match → =VLOOKUP(85, A2:B100, 2, TRUE)

Troubleshooting INDEX + MATCH

Common Issues:

• No match found → MATCH returns #N/A if no match exists.
• Case sensitivity confusion → MATCH with wildcards isn’t case-sensitive.
• Array formula confusion (older Excel) → Multiple criteria require Ctrl+Shift+Enter in legacy Excel.
• Off-by-one errors → If your range isn’t aligned, INDEX returns wrong results.

Fixes:

• Use IFERROR to handle missing matches:
  =IFERROR(INDEX(B2:B100, MATCH(“App*”, A2:A100, 0)), “Not Found”)
• For case-sensitive lookups:
  =INDEX(B2:B100, MATCH(TRUE, EXACT(“Apple”, A2:A100), 0))
• For multiple criteria in modern Excel, use:
  =INDEX(C2:C100, MATCH(1, (A2:A100=”Apple”)*(B2:B100=”East”), 0))

Troubleshooting FILTER

Common Issues:

• No matches found → FILTER returns #CALC! if nothing matches.
• Case sensitivity confusion → SEARCH is case-insensitive, FIND is case-sensitive.
• Dynamic array spill error → If there’s data blocking the output range, FILTER can’t spill.
• Legacy compatibility → FILTER is not available in Excel 2019 or earlier.

Fixes:

• Add a default value for empty results:
  =FILTER(A2:A100, ISNUMBER(SEARCH(“App”, A2:A100)), “Not Found”)
• Use FIND instead of SEARCH when you need case sensitivity.
• Make sure the spill range is empty (use =LET() to manage).
• If working with older Excel, simulate FILTER using INDEX/MATCH or array formulas.

Troubleshooting SEARCH and FIND

Common Issues:

• SEARCH vs FIND confusion → SEARCH is case-insensitive, FIND is case-sensitive.
• #VALUE! errors → Returned when text isn’t found.
• Hidden spaces or non-breaking spaces → Imported data often contains CHAR(160), which looks like a space but isn’t.
• Performance lag on large ranges.

Fixes:

• Wrap in ISNUMBER to create logical arrays:
  =ISNUMBER(SEARCH(“App”, A2:A100))
• Use CLEAN and SUBSTITUTE to sanitize data:
  =CLEAN(SUBSTITUTE(A2,CHAR(160),””))
• Combine with FILTER for multiple matches:
  =FILTER(B2:B100, ISNUMBER(SEARCH(“urgent”, A2:A100)), “Not Found”)

General Debugging Tips (Apply to All Methods)

• Check for leading/trailing spaces: Use =TRIM(A2).
• Check for non-visible characters: Use =CODE(MID(A2,n,1)) to reveal strange symbols.
• Confirm formula range alignment: The lookup array and return array must have the same number of rows.
• Test with helper columns: Break formulas into smaller parts to see where the logic fails.
• Use Evaluate Formula (Formulas > Evaluate Formula): Step through Excel’s calculation to see exactly what it’s matching.

With these troubleshooting techniques, you’ll be able to fix almost any failed lookup in Excel; whether it’s a missing match, a wildcard gone wrong, or a compatibility issue between modern and legacy functions.

Frequently Asked Questions (FAQ)

Q1. How do I do a partial text lookup in Excel?
Use wildcards (* and ?) in XLOOKUP or VLOOKUP. For example:

=XLOOKUP(“*apple*”, A2:A100, B2:B100, “Not Found”, 0)

This finds any text that contains “apple.”

Q2. How do I make Excel lookup case-sensitive?
Standard lookups ignore case. To force case-sensitivity, use EXACT:

=INDEX(B2:B100, MATCH(TRUE, EXACT(“Apple”, A2:A100), 0))

Q3. How do I return multiple matches in Excel instead of just one?
In modern Excel (Microsoft 365), you can use FILTER:

=FILTER(B2:B100, ISNUMBER(SEARCH(“App”, A2:A100)), “Not Found”)

This spills all matching values.

Q4. Can I do a lookup with multiple criteria?
Yes, by combining conditions in an array formula:

=INDEX(C2:C100, MATCH(1, (A2:A100=”Apple”)*(B2:B100=”East”), 0))

Q5. What’s the difference between SEARCH and FIND for lookups?

• SEARCH is case-insensitive.
• FIND is case-sensitive.
  Both return the position of a substring and can be combined with INDEX and MATCH for flexible lookups.

Q6. Should I use VLOOKUP or XLOOKUP?
Always prefer XLOOKUP if available. It’s easier to write, supports wildcards natively, doesn’t require column indexes, and handles errors more gracefully.

Final Thoughts

Mastering lookups in Excel goes far beyond typing =VLOOKUP(…) or =XLOOKUP(…). Once you unlock the power of wildcards, pattern searches, helper columns, and dynamic arrays, you begin to see Excel not as a static grid of numbers but as a flexible data engine capable of answering almost any question you ask it.

The techniques we explored, from “starts with” searches using wildcards, to multi-criteria INDEX/MATCH combinations, to FILTER formulas that return entire lists, are more than just tricks. They’re building blocks that let you automate reporting, clean messy datasets, and find patterns that would otherwise remain hidden. Just as importantly, the troubleshooting strategies ensure that when things don’t work as expected, you’ll know exactly how to dig into the problem and fix it with confidence.

If you take one thing away from this guide, let it be this: Excel lookups are not one-size-fits-all. Each method has its strengths, weaknesses, and ideal use cases. XLOOKUP is modern and powerful, but INDEX/MATCH remains a workhorse for compatibility; FILTER is unmatched for dynamic results, but SEARCH/FIND give you the granular control to detect patterns inside text. Knowing which tool to apply in the right situation is what separates casual users from true Excel power users.

Use this article as your reference whenever you’re stuck; whether you’re pulling customer emails by domain, flagging product codes by prefix, or troubleshooting a stubborn #N/A error. And remember: the more you practice combining these functions, the more natural and intuitive they’ll become. Excel continues to evolve, but the core principles of smart lookups will stay relevant for years to come. So bookmark this guide, share it with your team, and don’t be afraid to experiment. Your future self and your spreadsheets will thank you.