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 popular because we can use them with multiple criteria. Today, we will demonstrate two different methods to use the INDEX-MATCH formula with multiple criteria for partial text.

## 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**.

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

### 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 Name**, and the **Department **of some employees. We will return the search result based on some 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**.

- 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**.

🔎 **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.

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.**

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**.

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.

**Similar Readings**

- How to Use INDEX and Match for Partial Match
- Lookup Partial Text Match in Excel
- How to Use Formula for Partial Number Match in Excel

### 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 cells** G4 **to **G6**.

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**.

🔎**How Does the Formula Work?**

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

The **SEARCH **function looks for the value stored in cells** 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**.

- After that, type the partial texts in cell
**G4**to cell**G6**. - After typing the partial texts
**,**the**Search Result**will automatically be updated.

- Next, select cell
**B13**to**D13**. - Then, drag the
**Fill Handle**down to**Row 14**.

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

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

**Similar Readings**

- How to Use COUNTIF Function for Partial Match in Excel
- How to Highlight Partial Text in Excel Cell
- How to Find Partial Match in Two Columns in Excel

## 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**.

**Download Practice Book**

Download the practice book here.

## 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.