Excel Match Wildcard in Lookup Array (with 3 Formulas)

This article illustrates how to use excel formulas to match a wildcard in a lookup array. The three wildcards used in excel are the question mark (?), the asterisk (*), and the tilde (~).

The question mark is used to find a single character. For example, you can use sm?th as the lookup value to find “smith” or “smyth”. Moreover, you can use Jo??son to find “Johnson”. The asterisk is used to find any number of characters. For example, Rob* or *ert can be used to find Robert. The tilde is used so that excel does not consider the asterisk or question mark as wildcards. For example, Mar~* can be used to search for Mar* and not for Maria or Marshmallow.


Download Practice Workbook

You can download the practice workbook from the download button below.


3 Formulas to Match Wildcard in Lookup Array in Excel

We will use the following dataset to illustrate the methods. Assume you want to search for Mar* to get the name starting with these letters.


1. Match Wildcard in Lookup Array with INDEX-MATCH Formula

You can use the INDEX-MATCH formula to do that in excel. Type Mar* in cell E5 and enter the following formula in cell F5 to get the following result.

=IFERROR(INDEX(B5:B10,MATCH("*"&E5&"*",B5:B10,0)),"Not Found")

Match Wildcard in Lookup Array with INDEX-MATCH Formula

Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)


2. Apply VLOOKUP Formula to Match Wildcard in Lookup Array

You can also use the VLOOKUP function to get the same result. Apply the following formula instead in cell F5 for that.

=VLOOKUP("*"&E5&"*",B5:C10,1,FALSE)

VLOOKUP Formula to Match Wildcard in Lookup Array

Read More: VLOOKUP with Wildcard in Excel (3 Methods)


Similar Readings


3. Match Wildcard in Lookup Array with XLOOKUP Formula

Alternatively, you can use the XLOOKUP function for that. Enter the following formula in cell F5 to get the same result as in the earlier methods.

=XLOOKUP("*"&E5&"*",B5:B10,B5:B10,"Not Found",2,1)


Things to Remember

  • You may need to use CTRL+SHIFT+ENTER to enter the array formulas except in Excel365.
  • You need to use the proper wildcard as required. Use Mar* to search for texts with any length starting with “Mar”. But, you need to use Mar? to search for words with 4 letters only and starting with “Mar”.
  • If there is more than one matching result, the first value among them in the lookup array will be returned.

Conclusion

Now you know how to use excel formulas to match a wildcard to a lookup array. Which formula do you prefer the most? Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo