# Using the INDEX-MATCH Function with Multiple Criteria for a Partial Text in Excel – 2 Methods

## Excel INDEX Function Introduction

The INDEX function returns the value of a cell at the intersection of a row and column in an array or range.

• Syntax

INDEX (array, row_num, [col_num], [area_num])

• Arguments

array: It is the first compulsory argument: the constant array or the cell range.

row_num: It is the second compulsory argument: the row number in the array.

[col_num]: This is an optional argument: the column number in the array.

[area_num]: It is also an optional argument:Â  it selects a range in a reference and returns the intersection of row_num and col_num.

## Excel MATCH Function Introduction

The MATCH function looks for a specific value in an array or range and returns its relative position.

• Syntax

MATCH (lookup_value, lookup_array, [match_type])

• Arguments

lookup_value: This is the first compulsory argument: It is the value searched in a range or array.

lookup_array: It is the second compulsory argument. It is the array.

[match_type]: It is an optional argument: the type of match. For an exact match, use 0. Use 1Â and -1. 1Â to get the greatest value less than or equal to the lookup_value and -1 to get the smallest value greater than or equal to the lookup_value.

### Method 1 – Using the Excel INDEX-MATCH Formula with Multiple Criteria for a Partial Text

The datasetÂ  contains the First Name, Last Name, and the Department of employees. To return a result based on partial text:

STEPS:

• Create cells to insert the partial texts and display the Search Result. Here,Â  G4 and G5.

• Select C12 and enter the formula:
`=INDEX(\$B\$6:\$C\$10,SMALL(IF(ISNUMBER((SEARCH(\$G\$4,\$B\$6:\$B\$10))*(SEARCH(\$G\$5,\$C\$6:\$C\$10))),MATCH(ROW(\$B\$6:\$B\$10),ROW(\$B\$6:\$B\$10)),""),ROWS(\$A\$1:A1)),COLUMNS(\$A\$1:A1))`
• Press Enter.

Â Formula Breakdown

• SEARCH(\$G\$4,\$B\$6:\$B\$10)

looks for the value stored in G4 in B6:B10.

• SEARCH(\$G\$5,\$C\$6:\$C\$10)

looks for the value stored in G5 in C6:C10.

• (SEARCH(\$G\$4,\$B\$6:\$B\$10)*SEARCH(\$G\$5,\$C\$6:\$C\$10))

The asterisk (*) signÂ  multiplies the arrays.

• ISNUMBER(SEARCH(\$G\$4,\$B\$6:\$B\$10)*SEARCH(\$G\$5,\$C\$6:\$C\$10))

The ISNUMBER function checks if the value is a number in the multiplied array.

• IF(ISNUMBER((SEARCH(\$G\$4,\$B\$6:\$B\$10))*(SEARCH(\$G\$5,\$C\$6:\$C\$10))),MATCH(ROW(\$B\$6:\$B\$10),ROW(\$B\$6:\$B\$10)),””)

replaces the boolean values with the corresponding row numbers. The Row Function returns the row number of the cell and the MATCH function searches for a relative position in B6:B10.

• SMALL(IF(ISNUMBER((SEARCH(\$G\$4,\$B\$6:\$B\$10))*(SEARCH(\$G\$5,\$C\$6:\$C\$10))),MATCH(ROW(\$B\$6:\$B\$10),ROW(\$B\$6:\$B\$10)),””),ROWS(\$A\$1:A1)),COLUMNS(\$A\$1:A1)

returns the smallest row number in the array.

• INDEX(\$B\$6:\$C\$10,SMALL(IF(ISNUMBER((SEARCH(\$G\$4,\$B\$6:\$B\$10))*(SEARCH(\$G\$5,\$C\$6:\$C\$10))),MATCH(ROW(\$B\$6:\$B\$10),ROW(\$B\$6:\$B\$10)),””),ROWS(\$A\$1:A1)),COLUMNS(\$A\$1:A1))

returns the matched value.

• Drag the Fill Handle to the right.

The Search Result is Mike William (no partial textÂ  was entered in G4 and G5).

• Enter the partial text in G4 and G5. Here,Â  “Soin G4 and “Ta” in G5.
• Sophie Taylor is the Search Result.

The formula searches “So” in the First Name column and then, “Ta” in the Last Name column. As it found a match, it displays the result.

• If you change the partial text of the Last Name, it will display #NUM.

It finds a match in the First Name column, but the partial text of the Last Name does not match the value found in the First Name column.

• If you change the partial texts, the value will be updated.

### Method 2 – Applying the INDEX-MATCH Formula with Multiple Criteria for a Partial Text to Get Multiple Records

Use the INDEX and the MATCH functions with multiple criteria for a partial text to get multiple records.

To see the full name of the employee and his/her department in the search result:

STEPS:

• Select B13 and use the formula:
`=INDEX(\$B\$6:\$D\$10,SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(\$G\$4:\$G\$6),\$B\$6:\$D\$10))*1,{1;1;1})>0,MATCH(ROW(\$B\$6:\$D\$10),ROW(\$B\$6:\$D\$10)), ""),ROWS(\$A\$1:B1)),COLUMNS(\$A\$1:B1))`
• Press Enter.

Formula Breakdown

• SEARCH(TRANSPOSE(\$G\$4:\$G\$6), \$B\$6:\$D\$10)

looks for the value stored in G4:G6 in B6:D10. The TRANSPOSE function is used to transpose the values.

• ISNUMBER(SEARCH(TRANSPOSE(\$G\$4:\$G\$6), \$B\$6:\$D\$10))

converts the numbers to TRUE.

• MMULT(ISNUMBER(SEARCH(TRANSPOSE(\$G\$4:\$G\$6),\$B\$6:\$D\$10))*1,{1;1;1}

sums values row-wise.

• IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(\$G\$4:\$G\$6),\$B\$6:\$D\$10))*1,{1;1;1})>0,MATCH(ROW(\$B\$6:\$D\$10),ROW(\$B\$6:\$D\$10)), “”)

converts the non-numerical values to the corresponding row numbers.

• SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(\$G\$4:\$G\$6),\$B\$6:\$D\$10))*1,{1;1;1})>0,MATCH(ROW(\$B\$6:\$D\$10),ROW(\$B\$6:\$D\$10)), “”),ROWS(\$A\$1:B1)),COLUMNS(\$A\$1:B1))

The SMALL function returns the smallest row number in the array after finding the matches.

• INDEX(\$B\$6:\$D\$10,SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(\$G\$4:\$G\$6),\$B\$6:\$D\$10))*1,{1;1;1})>0,MATCH(ROW(\$B\$6:\$D\$10),ROW(\$B\$6:\$D\$10)), “”),ROWS(\$A\$1:B1)),COLUMNS(\$A\$1:B1))

returns the matched value.

• Drag the Fill Handle to the left toÂ D13. You will see Mikeâ€™s full name and department (there are no partial texts inÂ G4 to G6).

• Enter the partial texts in G4:G6.
• The Search Result will automatically be updated.

• Select Â B13:D13.
• Drag the Fill Handle down to Row 14.

• You will see Carryâ€™s full name and department because it matches theÂ partial text of theLast Name .

• Use the Fill Handle. It will display #NUM as no more matches are found.

## Use The FILTER & SEARCH Functions with Multiple Criteria for a Partial Text in Excel

Use the FILTER and SEARCH Functions. This is the same dataset and structure used in Method 1.

STEPS:

• SelectÂ C12 and enter the formula:
`=FILTER(\$B\$6:\$C\$10,ISNUMBER(SEARCH(\$G\$4, \$B\$6:\$B\$10)*SEARCH(\$G\$5, \$C\$6:\$C\$10)))`
• Press Enter to see the result.
• You will see the whole dataset as there is no partial text.

Formula Breakdown

• SEARCH(\$G\$4,\$B\$6:\$B\$10)

looks for the value inÂ G4 in B6:B10.

• SEARCH(\$G\$5,\$C\$6:\$C\$10)

looks for the value stored in G5 in C6:C10.

• (SEARCH(\$G\$4,\$B\$6:\$B\$10)*SEARCH(\$G\$5,\$C\$6:\$C\$10))

the asterisk (*) sign multiplies the arrays.

• ISNUMBER(SEARCH(\$G\$4,\$B\$6:\$B\$10)*SEARCH(\$G\$5,\$C\$6:\$C\$10))

checks if the value is a number in the multiplied array.

• FILTER(\$B\$6:\$C\$10,ISNUMBER(SEARCH(\$G\$4,\$B\$6:\$B\$10)*SEARCH(\$G\$5, \$C\$6:\$C\$10)))

filters the matched values.

• Enter “Hi” in G4 and “Ta” in G5.
• Press Enter to see an automatic update in the Search Result.

• If you change the partial texts, it will display all values containing the partial text.

• If it does not find a match, it will display #CALC.

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

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF