How to Use INDEX and Match for Partial Match: 2 Easy Ways

Method 1 – Using Wildcard Combining with INDEX and MATCH Functions for Finding Data with Partial Match

1.1 Match Starting Characters (Value&“*”)

Steps:

• Type the following formula in cell D16 and press Enter on your keyboard.
`=INDEX(\$C\$5:\$C\$13,MATCH(D15&"*",\$C\$5:\$C\$13,0))`
• You will be able to get the Darkmans which is a partial match of Dar.

Formula Explanation
• See the inner function, which is MATCH. In the first argument, D15&”*” is the lookup_value which matches the data with our partial text in the Model column. \$C\$5:\$C\$13 is the lookup_array. 0 is used to define the exact match.
• In the INDEX function, \$C\$5:\$C\$13 is the range where we will find the index, and MATCH(D15&”*”,\$C\$5:\$C\$13,0) is the row_num.
• The INDEX function returns the partial match.

1.2 Match Ending Characters (“*”&Value)

Steps:

• Select cell D16, and write down the following formula in that cell.
`=INDEX(\$C\$5:\$C\$13,MATCH("*"&D15,\$C\$5:\$C\$13,0))`
• Press Enter. Afterward, you will get your desired output of the INDEX and MATCH functions which are given in the below screenshot.

1.3 Match Any Number of Characters (“*”&Value&“*”)

Steps:

• Insert the following formula in cell D16 and press Enter.
`=INDEX(\$C\$5:\$C\$13,MATCH("*"&D15&"*",\$C\$5:\$C\$13,0))`
• You will be able to get The Cost of Living which is a partial match of Cos.

Method 2 – Retrieve Numerical Data by Combining INDEX and MATCH Functions for Partial Match with Multiple Criteria

Steps:

• To get the Book’s Name, type the below formula in cell D16 and press Enter.
`=INDEX(\$C\$5:\$C\$13,MATCH("*"&D15&"*",\$C\$5:\$C\$13,0))`

• Insert the below formula in cell D17 to find out the partial match of the Writer name, and again, press Enter.
`=INDEX(\$D\$5:\$D\$13,MATCH("*"&D15&"*",\$C\$5:\$C\$13,0))`

• You will get the Price of the matched book. For that, write down the below formula in cell D18 and press Enter.
`=INDEX(\$E\$5:\$E\$13,MATCH("*"&D15&"*",\$C\$5:\$C\$13,0))`

An Alternative Way with VLOOKUP Function for Extracting Data with Partial Match

Steps:

• Insert the formula in cell D16 and press Enter.
`=VLOOKUP("*"&D15&"*",B5:E13,1,FALSE)`
• You will be able to get the Harvest which is a partial match of arv.

Formula Explanation
• Inside the VLOOKUP function, “*” (asterisk) matches the data with our wildcard characters. & (ampersand) concatenates the asterisk and our entered keyword.
• B5:E13 this is the range where we will search the data.
• 1 define column numbers.
• FALSE defines the exact match.
Notes
Common Errors When they show
Match Problem There might be a matching problem when the dataset has conflicting data. Results will be based on the first matched data.
Asterisk (*) Matching Issue If the asterisk (*) sign is not used properly, for example, at the front, middle, or back, depending on the requirements, there could be some matching issues.
#N/A in VLOOKUP/INDEX/MATCH If the searched value is absent in the given dataset, all these functions will return this #NA error.

<< Go Back to Partial Match Excel | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!