Excel Formulas for Partial Text Matching in Database Columns

Manually matching messy, partial text entries against a master database is a notorious time-sink for data analysts. When reconciling standard funding sources, such as federal grants or private endowments, exact text matches rarely exist.

Utilizing a dynamic Excel formula grants you immediate clarity, seamlessly transforming fragmented strings into clean, relational insights. However, a key stipulation of this approach is carefully managing wildcard criteria to prevent false positives in large datasets.

Leading organizations tracking National Science Foundation awards rely on wildcard-infused XLOOKUP and SEARCH formulas to automate this reconciliation. Below, we outline the exact formulas, syntax rules, and configuration steps to master partial text matching.

Data normalization is one of the most common challenges Excel users face. You receive a transaction report, a list of customer queries, or a bank statement, and you need to map those entries to an official database. The problem? The names in your source sheet rarely match your database column perfectly. A transaction description might read "AMZN Mktp US*PR911", while your master vendor database simply lists "Amazon".

Performing an exact match using a standard VLOOKUP or XLOOKUP will fail in these scenarios, yielding frustrating #N/A errors. To bridge this gap, you need to master partial text matching in Excel. This comprehensive guide covers the best formulas and techniques to match partial text with a database column, ranging from simple wildcard searches to robust array formulas for complex, reverse-lookup scenarios.

Understanding the Two Types of Partial Matches

Before diving into the formulas, it is crucial to identify which type of partial match problem you are trying to solve. These require fundamentally different formula structures:

• Type A: Lookup value is short, Database values are long. You are looking up "Apple" in a database column containing "Apple Inc. Corp" or "Apple Retail Store".
• Type B: Lookup value is long, Database values are short. You are looking up "Microsoft Corp. Redmond WA" against a master list that contains only "Microsoft".

Method 1: XLOOKUP with Wildcards (Best for Type A Matches)

If you are using Excel 365 or Excel 2021, XLOOKUP is the most efficient and readable function for partial text matching. By default, XLOOKUP looks for exact matches, but it has a built-in parameter that enables wildcard matching.

The Wildcards: Asterisk (*) and Question Mark (?)

• * (Asterisk) represents any sequence of characters (including zero characters).
• ? (Question mark) represents any single character.

The Formula Syntax

To find a partial match where your search term is part of a longer string in your database column, use this syntax:

=XLOOKUP("*" & lookup_value & "*", database_lookup_column, database_return_column, "No Match", 2)

Step-by-Step Example

Imagine you have a lookup cell (A2) containing the word "Ford", and you want to find its matching ID in a database table where the name is listed as "Ford Motor Company".

Write this formula in cell B2:

=XLOOKUP("*" & A2 & "*", D:D, E:E, "Not Found", 2)

How it works:

• "*" & A2 & "*" concatenates asterisks to both sides of your search term, resulting in "*Ford*". This tells Excel to look for any cell in column D that contains "Ford".
• The last argument, 2, is critical. It instructs XLOOKUP to execute a "Wildcard character match". Without this, the formula will look for the literal string "*Ford*" and return an error.

Method 2: VLOOKUP with Wildcards (For Legacy Excel Versions)

If your organization uses older versions of Excel (like Excel 2016 or 2019) where XLOOKUP is unavailable, you can achieve the same Type A partial match behavior using VLOOKUP. Unlike XLOOKUP, VLOOKUP handles wildcards by default without needing a special mode argument.

The Formula Syntax

=VLOOKUP("*" & lookup_value & "*", database_table_range, col_index_num, FALSE)

Example Application

Using the same dataset as above, your formula would be:

=VLOOKUP("*" & A2 & "*", D:E, 2, FALSE)

Key Note: Always set the fourth argument to FALSE (or 0) to force an exact match structure. Even though we are matching a wildcard, setting it to FALSE forces Excel to find the exact pattern match of your wildcard string, rather than an approximate search on sorted data.

Method 3: Reverse Partial Match (Best for Type B Matches)

What if your situation is reversed? Your raw data contains a long string (e.g., "Chevron Gas Station Houston TX" in cell A2) and your database contains clean, short names (e.g., "Chevron" in column D).

If you search for "*" & A2 & "*", Excel will look for "*Chevron Gas Station Houston TX*" inside your database column. It will find nothing because your database column only contains the short word "Chevron".

To solve this, we must search the long cell for each of the short database entries. We can achieve this using a combination of LOOKUP, SEARCH, and ISNUMBER.

The Reverse Match Formula

=LOOKUP(2, 1/ISNUMBER(SEARCH(database_lookup_column, long_lookup_cell)), database_return_column)

Step-by-Step Breakdown

Let's unpack how this incredibly clever formula works:

1. SEARCH(database_lookup_column, long_lookup_cell): The SEARCH function is case-insensitive. When we pass an entire column of short database names as the search query against a single long cell, Excel searches for each database name inside that cell. It returns a number representing the starting position if found, and a #VALUE! error if not. This generates an array of numbers and errors, like: {#VALUE!; #VALUE!; 1; #VALUE!}.
2. ISNUMBER(...): Converts the array of numbers and errors into TRUE and FALSE values. Following our example, it becomes: {FALSE; FALSE; TRUE; FALSE}.
3. 1/ISNUMBER(...): Divides 1 by this array. In Excel, math operations treat TRUE as 1 and FALSE as 0.
   • 1 / TRUE (1) = 1
   • 1 / FALSE (0) = #DIV/0!
   This leaves us with an array of 1s and divide-by-zero errors: {#DIV/0!; #DIV/0!; 1; #DIV/0!}.
4. LOOKUP(2, ...): The LOOKUP function has a unique behavior: if it cannot find the lookup value (in this case, 2), it matches the *last numeric value* in the array that is less than or equal to the lookup value. Since all valid matches in our array are 1, and 2 is greater than 1, it will always match the last 1 (the row that contains the successful partial match), skipping all errors.
5. Finally, LOOKUP returns the corresponding value from your database_return_column at that matched position.

Method 4: Dynamic Filtering for Multiple Partial Matches

Sometimes, a partial match query can yield multiple matches. If you search for "Graph" in your database, you might want to return "Graphic Design", "Graphite Supplies", and "Graphics Corp". Standard lookups will only return the first match they find.

In modern Excel, you can use the FILTER function to dynamically extract all partial matching records into a list.

The Formula Syntax

=FILTER(database_range, ISNUMBER(SEARCH(lookup_value, database_lookup_column)), "No matches found")

Example

If you want to pull all database records from range D2:E100 where column D contains the partial text entered in cell A2:

=FILTER(D2:E100, ISNUMBER(SEARCH(A2, D2:D100)), "No matches")

This formula dynamically spills the results down and across adjacent cells, providing a live-updating, filtered subset of your database matching your criteria.

Case Sensitivity in Partial Matching

By default, both SEARCH and standard wildcard lookups are case-insensitive. If you must distinguish between case variations (e.g., matching "CAT" but ignoring "category" or "cat"), swap your functions:

• Use FIND instead of SEARCH. FIND is strictly case-sensitive.
• For example, to make the reverse lookup case-sensitive, modify the formula to:

  =LOOKUP(2, 1/ISNUMBER(FIND(database_lookup_column, long_lookup_cell)), database_return_column)

Troubleshooting & Performance Tips

Partial matching is computationally heavier than exact matching. If your Excel workbook begins to freeze or lag, consider these optimization strategies:

1. Limit Your Ranges

Avoid using whole-column references like D:D or E:E inside complex array formulas like the LOOKUP(2, 1/...) construct. This forces Excel to check over one million rows, dragging down calculation speeds. Instead, define explicit ranges (e.g., $D$2:$D$5000) or convert your database into an official Excel Table (using Ctrl + T) and use structured references (e.g., DatabaseTable[Company Name]).

2. Handle Duplicates Carefully

Remember that wildcard lookups return the first matching entry they encounter. If you have "Universal" and "Universal Studios" in your database column, searching for "*Universal*" might result in incorrect mapping depending on which row appears first in your source table. Clean your database table by sorting it alphabetically or from longest-to-shortest text string length to improve match accuracy.

3. Wrap in IFERROR to Clean up Reports

If no partial match exists, your formulas will return errors. Wrap your formulas in IFERROR to replace these unsightly indicators with a blank space or a warning flag:

=IFERROR(XLOOKUP("*" & A2 & "*", D:D, E:E, "No Match", 2), "Review Required")

Summary of Solutions