How to do first match in range with wildcard in Excel

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

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.

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.

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:

i need help with a formula that allows me to find the same value in another workbook then enter another value from the row it find the match. i know vlookup allows me to find the match but i dont know how to get the other value after it find the match.

An Excelchat Expert solved this problem in 30 mins!

Here is my challenge for you: You know how VLOOKUP looks at the first column of a defined range, matches it, and gives a result based on the match/none match? I have two columns I need to match: the first column is last name, the second column is first name. 99% of the time last name is sufficient, but the other 1% has multiple of the same last names, but first name is different, so everyone with the same last name gets the same result, when really only one of those should get the result. SO: Do you know how to write this formula??m This might also be a challenge, because the named range needs to be in alphabetical order to work properly, but obviously you cant alphabetize the first names as well. HMMMMMMMMMM..

An Excelchat Expert solved this problem in 22 mins!

Hi there, I'm looking for a formula to match a text within an array and return the results found. I know how to you use vlookup/match/index formula's, but I can only return the first match found. I would like to return the first match in cell A1 and the second match inside A2, third inside A3, etc. So when the first match is found and placed inside A1 then the formula inside A2 should ignore the first match that is already found and show the next match value. I would like to get this done with a formula and not with the autofilter option inside Excel.

An Excelchat Expert solved this problem in 29 mins!