Finding specific text within vast spreadsheets often frustrates professionals dealing with inconsistent data entry. While standard exact-match lookups address basic database queries, they fail when searching for partial text strings. Integrating wildcard characters with the modern XLOOKUP function grants users the flexibility to locate partial matches effortlessly. To succeed, one critical stipulation applies: you must explicitly set the match_mode argument to 2. For example, searching for "*Corp*" will seamlessly retrieve "DataCorp International." Below, we will demonstrate the exact formula syntax and configuration steps to master this advanced search technique.

Excel Formula to Search Specific Text with XLOOKUP

Excel's lookup functions have evolved significantly over the years. For decades, users relied on VLOOKUP, HLOOKUP, and the powerful INDEX and MATCH combination to query data. However, Microsoft revolutionized data retrieval with the introduction of XLOOKUP. This modern, versatile function is simpler, safer, and inherently more flexible.

One of the most common tasks in Excel is searching for specific text within a dataset. Whether you need to perform an exact match, find a partial text string, execute a case-sensitive search, or look up data using multiple text-based criteria, XLOOKUP handles it with ease. This comprehensive guide will walk you through the various ways to search for specific text using XLOOKUP, complete with syntax, practical examples, and step-by-step instructions.

Understanding the XLOOKUP Syntax

Before diving into specific text-searching scenarios, let's review the basic anatomy of the XLOOKUP function. Its syntax is as follows:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Here is a breakdown of the arguments:

• lookup_value (Required): The value you want to search for (e.g., the specific text).
• lookup_array (Required): The range or array where you want to perform the search.
• return_array (Required): The range or array from which you want to retrieve the corresponding result.
• [if_not_found] (Optional): The value to return if no match is found (e.g., "Not Found"). This eliminates the need for nesting IFERROR.
• [match_mode] (Optional): Specifies how to match the lookup_value:
  • 0: Exact match (default).
  • -1: Exact match or next smaller item.
  • 1: Exact match or next larger item.
  • 2: Wildcard match (essential for partial text searches).
• [search_mode] (Optional): Specifies the search direction (e.g., first-to-last, last-to-first).

Scenario 1: Exact Match Text Search

By default, XLOOKUP performs an exact match search. Unlike VLOOKUP, you do not need to pass FALSE or 0 as an argument to ensure an exact match. It is the default behavior.

Example Dataset:

If you want to find the exact price of the "Mechanical Keyboard", you can write the following formula:

=XLOOKUP("Mechanical Keyboard", B2:B5, D2:D5, "Product Not Found")

How It Works:

• "Mechanical Keyboard" is the specific text you are looking for.
• B2:B5 is the range containing product names.
• D2:D5 is the range containing the prices you want to return.
• If the product name is not found, the formula will gracefully return "Product Not Found".

Scenario 2: Partial Match Search with Wildcards

Often, you won't know the exact spelling or full name of the text you are looking for. For instance, you might want to search for "Chair" and return the details for "Ergonomic Office Chair". To do this with XLOOKUP, you must use wildcard characters and set the [match_mode] argument to 2.

Wildcard Characters in Excel:

• Asterisk (*): Represents any number of characters. For example, "*Chair*" matches any string containing "Chair".
• Question Mark (?): Represents a single character. For example, "P-10?" matches "P-101", "P-102", etc.

The Formula Structure:

To perform a partial search for a text term stored in cell F2 (e.g., "Keyboard"), use this formula:

=XLOOKUP("*" & F2 & "*", B2:B5, D2:D5, "No Match Found", 2)

Key Points for Wildcard Search:

1. Concatenation: We concatenate the asterisk wildcards to the target cell: "*" & F2 & "*". This tells Excel to look for any cell in the lookup array that contains the text within cell F2, regardless of what text precedes or follows it.
2. Match Mode = 2: You must explicitly set the fifth argument (match_mode) to 2. If you omit this, XLOOKUP will search literally for the characters "*" and "&", resulting in a "No Match Found" error.

Scenario 3: Case-Sensitive Text Search

By default, Excel formulas (including standard XLOOKUP, VLOOKUP, and MATCH) are case-insensitive. This means searching for "MOUSE", "Mouse", or "mouse" yields the exact same result.

If you must differentiate between text with different casing (e.g., searching for product codes like "abC" vs. "ABC"), you can combine XLOOKUP with the EXACT function.

The Formula:

=XLOOKUP(TRUE, EXACT(F2, B2:B5), D2:D5, "No Case-Sensitive Match")

How It Works:

• The EXACT(F2, B2:B5) function compares the lookup value in F2 against every element in the range B2:B5. It performs a strict case-sensitive comparison and returns an array of TRUE or FALSE values. For example: {FALSE; TRUE; FALSE; FALSE}.
• XLOOKUP then searches for the logical value TRUE within that generated array and returns the corresponding item from the return array D2:D5.

Scenario 4: Search with Multiple Text Criteria

Sometimes your search depends on matching specific text across multiple columns. For example, you might want to find the price of an item that is in the "Furniture" category AND has the name "Standing Desk".

XLOOKUP allows you to search across multiple columns by using array multiplication (representing the AND logic).

The Formula:

=XLOOKUP(1, (B2:B5="Standing Desk") * (C2:C5="Furniture"), D2:D5, "Criteria Not Met")

How It Works:

• (B2:B5="Standing Desk") evaluates each cell in column B, returning an array of TRUE/FALSE values: {FALSE; FALSE; FALSE; TRUE}.
• (C2:C5="Furniture") evaluates each cell in column C, returning: {FALSE; FALSE; TRUE; TRUE}.
• Multiplying these two arrays together converts the boolean values into 1s and 0s (where TRUE * TRUE = 1, and any other combination equals 0). This yields the array: {0; 0; 0; 1}.
• XLOOKUP searches for the value 1 in this resulting array and returns the value from the matching position in D2:D5.

Scenario 5: Handling Missing Text with XLOOKUP

Traditional lookup formulas require nesting inside an IFERROR or IFNA function to prevent ugly #N/A errors from cluttering your spreadsheet. XLOOKUP has an elegant, built-in solution using its fourth argument: [if_not_found].

Simply define what you want to appear when a specific text search fails:

=XLOOKUP("Gaming Laptop", B2:B5, D2:D5, "Item Out of Stock")

If "Gaming Laptop" is not in your product list, the cell will simply display Item Out of Stock instead of throwing an error.

XLOOKUP vs. VLOOKUP for Text Searching

If you are still using VLOOKUP for text searches, here is a quick comparison of why XLOOKUP is the superior choice:

Pro-Tips for Searching Text with XLOOKUP

1. Trim Excess Spaces: Invisible leading or trailing spaces are the number one cause of failed text lookups. If your lookup value or database has irregular spacing, wrap the ranges in the TRIM function inside your lookup:

   =XLOOKUP(TRIM(F2), TRIM(B2:B5), D2:D5)

2. Ensure Correct Match Mode for Wildcards: Remember that if you use asterisks (*) for partial matches, you must set the fifth argument to 2. Omitting this is the most common mistake made by users transitioning from VLOOKUP.
3. Vertical vs. Horizontal: XLOOKUP replaces both VLOOKUP and HLOOKUP. It automatically detects whether your arrays are oriented vertically or horizontally and performs the search seamlessly.

Conclusion

The XLOOKUP function is a powerful asset for clean, robust, and versatile text searches in Excel. Whether you are dealing with simple exact matches, flexible wildcard partial searches, case-sensitive lookups, or multi-column criteria, XLOOKUP provides a cleaner syntax and minimizes error risks compared to older formulas. Transitioning to XLOOKUP will not only save you time but also make your Excel workbooks significantly more resilient to structural updates.