Xlookup Wildcard Does Not Work Wth Number

MIster jashon

5 min read

·

Oct 02, 2024

41

Share

The Mystery of XLOOKUP and Wildcards with Numbers: Why Does it Not Work?

The XLOOKUP function is a powerful tool in Excel, allowing for flexible and efficient data retrieval. However, you might encounter a peculiar issue when using wildcards within XLOOKUP for numerical data. The problem arises because XLOOKUP is designed to perform an exact match by default. This means that when you attempt to use wildcards with numbers, it may not behave as expected.

Let's illustrate this with an example:

Imagine you have a list of product IDs in column A and corresponding prices in column B.

You want to find the price of a product with an ID starting with "123". You might think that using a wildcard like 123* in XLOOKUP would work, but it won't.

Why does XLOOKUP struggle with wildcards and numbers?

It's all about how XLOOKUP interprets its arguments.

• Exact Matching: When XLOOKUP encounters a wildcard character (* or ?), it interprets it as a literal part of the search string. This means it will look for an exact match of the wildcard pattern, not partial matches.

• Number vs. Text: Numbers in Excel are treated differently from text. XLOOKUP will treat 123* as a literal number and won't consider it a wildcard pattern.

So, how can we find the price of a product starting with "123" using XLOOKUP?

Here's the solution:

1. Convert Numbers to Text: The trick is to convert the numbers in your lookup column (in our case, the Product ID) to text. This can be done using the TEXT function.

2. Utilize the TEXT Function: In your XLOOKUP formula, use the TEXT function to convert the lookup value and the lookup array to text.

3. Apply Wildcards: Now, you can use the wildcard pattern 123* within the XLOOKUP function, and it will find all entries starting with "123".

Here's an example formula:

=XLOOKUP(TEXT("123*", "0"), TEXT(A:A, "0"), B:B)

This formula will:

1. Convert "123" to text:* The TEXT function converts the wildcard string "123*" to text, allowing for wildcard interpretation.
2. Convert Product IDs to text: The TEXT function converts the entire lookup array (column A) to text.
3. Perform the XLOOKUP: The XLOOKUP function finds the matching text value in column A and retrieves the corresponding price from column B.

Important Note: The TEXT function is a crucial tool for overcoming this limitation. Remember, XLOOKUP excels at exact matches, and by converting your numbers to text, you can effectively use wildcards with numbers in XLOOKUP.

Conclusion:

The limitation of using wildcards directly with numbers in XLOOKUP is due to its default exact matching behavior. By leveraging the TEXT function to convert numerical data to text, you can effectively utilize wildcards within XLOOKUP, unlocking its full potential for flexible and efficient data retrieval.