How to Use the INDEX and MATCH Functions with Wildcards in Google Sheets for Partial Text Lookups

Searching for data in Google Sheets when you only know a portion of a customer name, transaction code, or product SKU is a frequent spreadsheet challenge. While modern functions like XLOOKUP and FILTER are excellent, millions of production workbooks and legacy spreadsheet models rely on the classic INDEX and MATCH combination for compatibility across Excel and Google Sheets. By default, MATCH performs exact lookups. However, by leveraging wildcard characters inside the MATCH function, you can search for records based on partial substrings, prefixes, or suffixes with precision.

Understanding the Mechanics: INDEX, MATCH, and Wildcards

The INDEX and MATCH formula functions as a coordinated lookup engine:

• INDEX(return_range, row_num): Retrieves the value from a specific row number in the target return column.
• MATCH(search_key, lookup_range, [match_type]): Scans the lookup column and returns the relative row position of the match. For exact/wildcard matching, match_type must be set to 0.

Supported Wildcard Operators in MATCH

When match_type is set to 0, Google Sheets’ MATCH function natively supports three wildcard characters:

• * (Asterisk): Matches any sequence of zero or more characters.
• ? (Question Mark): Matches exactly one single character.
• ~ (Tilde): Escapes a literal asterisk or question mark (e.g. ~* searches for an actual asterisk).

1. Basic Substring Search: Finding a Record Containing Text

Suppose you have customer data in cells A2:C20:

• Column A: Customer Name (e.g. Dr. Elizabeth Johnson, Michael Smith)
• Column B: Account ID (e.g. ACC-8812)
• Column C: Balance (e.g. 1,450)

You want to find the Balance for an account where you only know the last name is Johnson. In cell E2, enter:

=INDEX(C2:C20, MATCH("*Johnson*", A2:A20, 0))

How It Works: *Johnson* matches any cell in column A that contains “Johnson” anywhere inside the string. MATCH returns the row index, and INDEX retrieves the corresponding value from column C.

2. Prefix and Suffix Partial Matching

You can control where the wildcard applies to enforce prefix or suffix matching:

Prefix Search (Begins With)

To match an Account ID starting with ACC-88:

=INDEX(A2:A20, MATCH("ACC-88*", B2:B20, 0))

Suffix Search (Ends With)

To match a customer whose email address ends with @digitash.com:

=INDEX(A2:A20, MATCH("*@digitash.com", D2:D20, 0))

3. Dynamic Wildcard Lookups Referencing User Input Cells

Hardcoding search terms inside quotes limits dashboard interactivity. To allow users to type partial keywords into an input cell (e.g. cell F1), concatenate wildcard asterisks using the ampersand (&) operator:

=INDEX(C2:C20, MATCH("*" & F1 & "*", A2:A20, 0))

If the user types Eliz into cell F1, the formula evaluates "*Eliz*" and retrieves Elizabeth Johnson’s balance immediately.

4. Using Single-Character Question Mark Wildcards

When searching for fixed-pattern codes where specific digits vary, use question marks (?):

Suppose product codes are formatted as PRD-A-01, PRD-B-01. To match any product with version 01 regardless of whether the category letter is A, B, or C:

=INDEX(C2:C20, MATCH("PRD-?-01", A2:A20, 0))

Handling Missing Matches with IFERROR

If a partial keyword does not exist in the lookup column, MATCH returns a #N/A error. Wrap your formula inside IFERROR to display friendly feedback:

=IFERROR(INDEX(C2:C20, MATCH("*" & F1 & "*", A2:A20, 0)), "No Matching Record Found")

Combining INDEX and MATCH with wildcards delivers robust, backward-compatible substring lookup capabilities across any Google Sheets workbook.