INDEX-MATCH with Multiple Criteria for Partial Text in Excel (2 Ways)

In this article, we will learn to use the INDEX-MATCH Formula with multiple criteria for partial text. We use INDEX and MATCH Functions to look up values in an array. These functions are very much popular because we can use them with multiple criteria also. Today, we will demonstrate two different methods to use INDEX-MATCH Formula with Multiple Criteria for Partial Text.


Download Practice Book

Download the practice book here.


Excel INDEX Function Introduction

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

  • Syntax

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

  • Arguments

array: This is the first compulsory argument of the INDEX Function. It is the constant array or the cell range.

row_num: It is the second compulsory argument and denotes the row number from the desired array.

[col_num]: This is an optional argument that represents the column number of the desired array.

[area_num]: It is also an optional argument that selects a range in a reference. It 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 the relative position of that value in the array or range.

  • Syntax

MATCH (lookup_value, lookup_array, [match_type])

  • Arguments

lookup_value: This is the first compulsory argument of the MATCH Function. It is the value that we search in a range or array.

lookup_array: It is the second compulsory argument. It is the array where we search for the value.

[match_type]: It is an optional argument that represents the type of match we want. For example, if you want an exact match, you need to type 0 in the third argument. You can also type 1 and -1. 1 gets the greatest value which is less than or equal to the lookup_value and -1 gets the smallest value which is greater than or equal to the lookup_value.


2 Ways to Use INDEX-MATCH Formula with Multiple Criteria for Partial Text in Excel

1. Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

In the first method, we will use the INDEX and MATCH Functions with multiple criteria for partial text to get the full name of an employee. Here, we will use a dataset that contains information about the First and Last Names, and the Departments of some employees. We will return the search result based on some partial text.

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

Let’s follow the steps below to learn this method.

STEPS:

  • In the first place, create cells for inserting the partial texts and displaying the search result. Here, we will insert the partial text in Cell G4 & G5.

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

  • Secondly, select Cell C12 and type 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))
  • Then, hit Enter.

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

🔎 How Does the Formula Work?

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

The SEARCH Function looks for the value stored in Cell G4 in the range B6:B10.

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

Here, this SEARCH Function looks for the value stored in Cell G5 in the range C6:C10.

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

Here, the asterisk (*) sign is multiplying 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 or not 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)),””)

Here, this formula will replace 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 the range 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)

This formula will return the smallest row number in the array that we get after finding the matches.

  • 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))

Finally, the INDEX Function returns the matched value.

  • After that, drag the Fill Handle to the right cell.

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

Here, the Search Result is showing Mike William because we have not entered any partial text in Cell G4 and G5 yet.

  • Next, type the partial text in Cell G4 and G5. Here, we have typed So in Cell G4 and Ta in Cell G5.
  • After typing the partial texts, we will see Sophie Taylor in the Search Result.

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

Here, the formula first searches So in the First Name column and then, Ta in the Last Name column. As it found a match, it displayed the result.

  • Now, if we change the partial text of the Last Name, it will display #NUM.

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text

Here, it finds a match in the First Name column, but the partial text of the Last Name does not match with the value that is found in the First Name column.

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

Excel INDEX-MATCH Formula with Multiple Criteria for Partial Text


2. Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

In this method, we will use the INDEX and MATCH Functions with multiple criteria for partial text to get multiple records. Here, we will see the full name of the employee and his/her department in the search result. Again, we will use the same dataset and structure. It will display the full record of the employee if any partial text matches. We will type the partial texts in Cell G4 to G6.

Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

Let’s pay attention to the steps below to know more.

STEPS:

  • In the beginning, select Cell B13 and type 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))
  • Then, hit Enter.

Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

🔎How Does the Formula Work?

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

The SEARCH Function looks for the value stored in Cell G4 to G6 in the range B6:D10. The TRANSPOSE Function is used to transpose the values.

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

Here, this ISNUMBER Function converts the numbers to TRUE.

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

Here, the MMULT Function 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)), “”)

Here, this formula 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))

This SMALL Function will return the smallest row number in the array that we get 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))

Finally, the INDEX Function returns the matched value.

  • Now, drag the Fill Handle to the left to Cell D13. You will see Mike’s full name and department because there are no partial texts in Cell G4 to G6.

Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

  • After that, type the partial texts in Cell G4 to Cell G6.
  • After typing the partial texts, the Search Result will automatically be updated.

Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

  • Next, select Cell B13 to D13.
  • Then, drag the Fill Handle down to Row 14.

Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

  • Here, you will see Carry’s full name and department because it matches with the Last Name partial text.

Apply INDEX-MATCH Formula with Multiple Criteria for Partial Text to Get Multiple Records

  • Again, if you use the Fill Handle, it will display #NUM as it doesn’t find any more matches.


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

There is another easy way to display search results based on multiple criteria for partial text. But we can apply this method in Excel 365 only. In this method, we will use the FILTER and SEARCH Functions. Here, we will use the same dataset and structure that we used in Method-1.

Let’s observe the steps below to learn the alternative method.

STEPS:

  • Firstly, select Cell C12 and type the formula:
=FILTER($B$6:$C$10,ISNUMBER(SEARCH($G$4, $B$6:$B$10)*SEARCH($G$5, $C$6:$C$10)))
  • Hit Enter to see the result.
  • You will see the whole dataset as there is no partial text.

🔎 How Does the Formula Work?

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

The SEARCH Function looks for the value stored in Cell G4 in the range B6:B10.

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

Here, this SEARCH Function looks for the value stored in Cell G5 in the range C6:C10.

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

Here, the asterisk (*) sign is multiplying 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 or not in the multiplied array.

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

Finally, the FILTER Function filters the matched values.

  • Secondly, type Hi in Cell G4 and Ta in Cell G5.
  • Now, hit Enter to see an automatic update in the Search Result.

  • Again, if we change the partial texts, it will display all the values that contain the partial text.

  • On the other hand, if it does not find any match, it will display #CALC.


Conclusion

We demonstrated 2 easy methods to use INDEX and MATCH Functions with multiple criteria for partial text. We have discussed an alternative method. I hope these methods will help you to perform your tasks perfectly. Furthermore, we have also added the practice book at the beginning of the article. You can download it to practice more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo