How to Use XLOOKUP to Find Values That Start With Specific Text in Excel

XLOOKUP is a new function in Excel that allows you to look up a value in a range or table and return a corresponding value from another range or table. It has the following syntax:

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

• lookup_value is the value you want to find.
• lookup_array is the range or table where you want to look for the lookup_value.
• return_array is the range or table from which you want to return a value.
• [if_not_found] is an optional argument that specifies what to return if the lookup_value is not found. The default is #N/A.
• [match_mode] is an optional argument that specifies how to match the lookup_value with the values in the lookup_array. The default is 0 (exact match). You can also use 1 (exact match or next smaller), -1 (exact match or next larger), 2 (wildcard match), or -2 (binary search).
• [search_mode] is an optional argument that specifies the direction to search the lookup_array. The default is 1 (first to last). You can also use -1 (last to first), 2 (binary search ascending), or -2 (binary search descending).

Procedures

To use XLOOKUP to prioritise values that start with certain text, you need to use the wildcard match mode (2) and concatenate the text you want to prioritise with an asterisk (*) at the beginning. For example, if you want to prioritise values that start with “HTJ”, you can use the following formula:

=XLOOKUP("HTJ*"&lookup_value, lookup_array, return_array, [if_not_found], 2, [search_mode])

This formula will look for the first value in the lookup_array that starts with “HTJ” and matches the lookup_value. If no such value is found, it will look for any other value that matches the lookup_value. If no match is found, it will return the [if_not_found] value.

Explanation

To illustrate how this formula works, let’s use an example scenario. Suppose you have a table of product codes and quantities, and you want to look up the quantity for a given product code. However, you want to prioritise the product codes that start with “HTJ”, as they are more important for your business. The table looks like this:

To use XLOOKUP to prioritise values that start with “HTJ”, you can use the following formula in cell F2, and copy it down to F11:

=XLOOKUP("HTJ*"&E2,B2:B11,C2:C11,"no match",2)

The formula works as follows:

• In cell F2, the formula concatenates “HTJ” with the value in E2, which is “ABC”. The result is “HTJABC”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “ABC”. Since there is no such value, the formula looks for any other value that matches “ABC”.
• The formula finds a match in B3, which is “001-ABC-123”, and returns the corresponding value from C3, which is 10.
• In cell F3, the formula concatenates “HTJ” with the value in E3, which is “DEF”. The result is “HTJDEF”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “DEF”. Since there is no such value, the formula looks for any other value that matches “DEF”.
• The formula finds a match in B4, which is “002-DEF-456”, and returns the corresponding value from C4, which is 20.
• In cell F4, the formula concatenates “HTJ” with the value in E4, which is “GHI”. The result is “HTJGHI”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “GHI”. Since there is no such value, the formula looks for any other value that matches “GHI”.
• The formula finds a match in B5, which is “003-GHI-789”, and returns the corresponding value from C5, which is 30.
• In cell F5, the formula concatenates “HTJ” with the value in E5, which is “JKL”. The result is “HTJJKL”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “JKL”. Since there is no such value, the formula looks for any other value that matches “JKL”.
• The formula finds a match in B6, which is “004-JKL-012”, and returns the corresponding value from C6, which is 40.
• In cell F6, the formula concatenates “HTJ” with the value in E6, which is “MNO”. The result is “HTJMNO”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “MNO”. Since there is no such value, the formula looks for any other value that matches “MNO”.
• The formula finds a match in B7, which is “005-MNO-345”, and returns the corresponding value from C7, which is 50.
• In cell F7, the formula concatenates “HTJ” with the value in E7, which is “PQR”. The result is “HTJPQR”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “PQR”. Since there is no such value, the formula looks for any other value that matches “PQR”.
• The formula finds a match in B8, which is “006-PQR-678”, and returns the corresponding value from C8, which is 60.
• In cell F8, the formula concatenates “HTJ” with the value in E8, which is “STU”. The result is “HTJSTU”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “STU”. Since there is no such value, the formula looks for any other value that matches “STU”.
• The formula finds a match in B9, which is “007-STU-901”, and returns the corresponding value from C9, which is 70.
• In cell F9, the formula concatenates “HTJ” with the value in E9, which is “HTJ”. The result is “HTJHTJ”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “HTJ”. The formula finds a match in B10, which is “008-HTJ-234”, and returns the corresponding value from C10, which is 80.
• In cell F10, the formula concatenates “HTJ” with the value in E10, which is “HTJ”. The result is “HTJHTJ”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “HTJ”. The formula finds a match in B10, which is “008-HTJ-234”, and returns the corresponding value from C10, which is 80.
• In cell F11, the formula concatenates “HTJ” with the value in E11, which is “HTJ”. The result is “HTJHTJ”.
• The formula then looks for the first value in B2:B11 that starts with “HTJ” and matches “HTJ”. The formula finds a match in B10, which is “008-HTJ-234”, and returns the corresponding value from C10, which is 80.

Excel table

The following table shows the result of the formula in F2:F11: