Excel’s XLOOKUP function does not natively support wildcards like the asterisk (*) or question mark (?) in its search arguments. To achieve wildcard-like behavior, you must wrap the lookup value with the IFNA and ISNUMBER functions combined with SEARCH or FIND. This workaround allows partial text matching, effectively replacing older VLOOKUP methods while maintaining the robustness of XLOOKUP.
The Limitation of Native Wildcards
When users migrate from legacy Excel functions to XLOOKUP, a common point of confusion arises regarding pattern matching. In traditional VLOOKUP or INDEX/MATCH scenarios, placing an asterisk before or after a search term would trigger Excel's built-in wildcard recognition. However, XLOOKUP was designed with strict data type integrity in mind. It treats every argument as a literal value unless explicitly told otherwise by a logical wrapper.
This design choice prevents ambiguous matches but breaks existing workflows that relied on simple wildcard syntax. For instance, if you attempt to use XLOOKUP("*Smith*", A:A, B:B), Excel will return a #N/A error because it searches for the literal string containing asterisks rather than any name ending in Smith. Understanding this distinction is crucial for anyone looking to modernize their spreadsheet logic without losing flexibility.
Implementing Wildcard Logic with IFNA and SEARCH
The standard solution involves constructing a formula that tests whether the lookup value exists within the target range using substring matching. The core mechanism relies on the SEARCH function, which returns the starting position of a substring if found, or an error if it is not. By wrapping this in ISNUMBER, we convert the result into a boolean true/false value.
To make this work seamlessly with XLOOKUP, you need to handle cases where no match is found gracefully. The IFNA function is ideal for this purpose. It checks if the SEARCH operation fails; if it does, it can return a default value or allow the XLOOKUP to proceed with a different strategy. Here is the general structure for finding a partial match:
=XLOOKUP(1, ISNUMBER(SEARCH("partial_text", lookup_range)), return_range, "Not Found")In this formula, the first argument 1 acts as the lookup value. The second argument creates an array of TRUE/FALSE values where TRUE indicates a match. XLOOKUP then finds the first TRUE value and returns the corresponding item from the return_range. This method effectively simulates wildcard behavior by checking for containment rather than exact equality.
Handling Case Sensitivity
A critical detail in this approach is case sensitivity. The SEARCH function is case-insensitive, meaning it will find "smith" even if the cell contains "Smith". If your data requires case-sensitive matching, you should substitute SEARCH with FIND. However, be aware that FIND will throw an error if the case does not match exactly, so ensure your logic accounts for this potential failure mode.
Advanced Scenarios: Multiple Criteria and Positioning
Often, wildcard functionality is required alongside other constraints. For example, you might need to find a product name containing specific keywords within a particular category. In such cases, you can combine multiple ISNUMBER(SEARCH(...)) conditions using multiplication or addition inside the XLOOKUP criteria array. This allows for complex filtering without resorting to helper columns.
If you are dealing with two distinct criteria, consider reviewing our guide on positioning criteria correctly. Properly structuring these arrays ensures that XLOOKUP evaluates all conditions simultaneously rather than sequentially, which can lead to incorrect results if the order is mismanaged. See Excel XLOOKUP Formula: Positioning Criteria with Two Criteria for detailed structural advice.
Common Pitfalls and Error Handling
One frequent error when implementing this workaround is the #NAME? error. This typically occurs if the function names are misspelled or if the add-in required for XLOOKUP is not enabled in older versions of Excel. Before attempting complex wildcard formulas, verify that your basic XLOOKUP syntax works. If you encounter #NAME?, check your Excel version and ensure you are using the correct function syntax for your locale.
Another issue is performance. Because this method iterates through an entire array to evaluate each cell, it can be slower on very large datasets compared to exact matches. If performance becomes a bottleneck, consider using Power Query for data transformation or limiting the scope of your lookup ranges to only the relevant rows.
Alternative Approaches
While the SEARCH + ISNUMBER method is the most direct replacement for wildcards, some users prefer using FILTER for dynamic arrays. If you are using Microsoft 365, FILTER can return multiple matches instead of just the first one, which might be more appropriate for certain reporting tasks. Additionally, for large workbooks, moving away from volatile lookup functions entirely might be beneficial. Consider exploring how query tables can improve efficiency, as discussed in Improve Functionality of Large Excel Workbooks with Hundreds of Sheets: Use Query Tables and XLOOKUP instead of INDIRECT.
Frequently Asked Questions
Can I use the asterisk symbol directly in XLOOKUP?
No, XLOOKUP treats the asterisk as a literal character, not a wildcard. You must use the IFNA and SEARCH combination to simulate wildcard behavior.
Is this method case-sensitive?
By default, using the SEARCH function makes it case-insensitive. If you need case-sensitive matching, replace SEARCH with FIND, but be prepared to handle potential errors if cases do not align perfectly.
What happens if there are multiple matches?
XLOOKUP will return only the first match it encounters in the range. If you need all matching rows, consider using the FILTER function instead of XLOOKUP.
Why am I getting a #NAME? error?
This usually indicates a typo in the function name or that your Excel version does not support XLOOKUP. Ensure you are using Excel 2021 or Microsoft 365.
Does this workaround affect calculation speed?
Yes, evaluating an array for substring matches is computationally heavier than exact lookups. On very large sheets, this may cause noticeable lag during recalculation.