Md. Shamim Reza
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.
Excel Match Wildcard in Lookup Array: 3 Formulas
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")
Read More: How to Use Wildcards in Excel
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)
Read More: Search for Question Mark in Excel
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.
Download Practice Workbook
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. Stay with us and keep learning.
