Go Back

How to Find the First Match in a Range with a Wildcard

Figure 1. of the Index and Match Operation in Excel.

In order to determine the value of the first match in an array of data, with the use of a Wildcard, we can utilize an Index and Match operation syntax, configured to find an exact match.

Generic Formula

=INDEX(range,MATCH(val&"*",range,0))

How the Function Operates

  1. First, we must properly identify and arrange the data set we intend to index and match in our Excel worksheet. In the example below we are working with a data set of courses offered by three (3) different university Students in a semester.

Figure 2. of Data set for Indexing and Matching in an Excel sheet.

  1. Input the Tag/Search name of the value for which we want the range values, in the next column of our worksheet.

Figure 3. of Search name entered.

  1. Label the next column as “Result” and then we can now proceed to input the formula syntax

=INDEX(A2:D2,MATCH(D2&"*",A2:D2,0)) within cell E2, to obtain our Index and Match result.

Figure 4. of entering formula for using First Match in Range with Wildcard.

In this post, we have illustrated how we can utilize the MATCH Function to identify the position of the first match within the cell range A2:C2 of our worksheet. Our look_up value is based on the value in A2 connected with an asterisk (*) utilized as a Wildcard. Our match_type  is set to 0, so that we can obtain an exact match. The goal is to obtain the first match within an array by using a Wildcard Function.

Figure 5. of Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello, Im having trouble with a question: I have column full of 1's and 0's I want to take a range ex. (B2:B100) and then i want to take this range use the match function to find the first 1 value, "=Match(1,B2:B100,0)" then once i have the location i want to change the selected array. for example, lets say that the first value of one appears in B5 so the match function would return 4, so then i would take the range B5:B100 for the next calculation to find the next 1 value, i tried (B(2+4):B100) but it gives an error
Solved by B. S. in 30 mins
I have two lists. One is a list of part numbers. The other is a list of long text descriptions. I can use VLOOKUP with "*"&cell address"*" to find the first match. But how can I find the second and third matches? I have tried using INDEX and SHORT but the wild cards no longer find the match like they do in VLOOKUP. Can you help?
Solved by S. J. in 12 mins
Hi, I have problem with formula MATCH and reference cell. I have created a column of range values, for example like this: Z1=A1:A10, Z2=B1:B10, Z3=C1:C10... I first use VLOOKUP to find the cell with range I need and that I use the result in MATCH formula instead of range - cell reference to the range. I get N/A error. I think the problem is that Vlookup returns "range" value as text. I tried multiple ways to change it to general or number data type but nothing works. Also I tried to go around and use combination of index and match to come to the same result but it returns the same N/A value. I am out of options how to fix it. Any advice?
Solved by Z. Y. in 21 mins

Leave a Comment

avatar