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.

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.

**Read More:** **How to Use INDEX MATCH with Multiple Criteria for Date Range**

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

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

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

**Read More:** **VBA INDEX MATCH Based on Multiple Criteria in Excel (3 Methods)**

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

## Related Articles

**Vlookup with Multiple Criteria without a Helper Column in Excel (5 Ways)****VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)****XLOOKUP with Multiple Criteria in Excel (4 Easy Ways)****How to Lookup Across Multiple Sheets in Excel (3 Methods)****Lookup and Return Multiple Values Concatenated into One Cell in Excel**