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

• Then, hit Enter.

🔎 How Does the Formula Work?

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

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:

• Then, hit Enter.

🔎How Does the Formula Work?

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

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:

• Hit Enter to see the result.
• You will see the whole dataset as there is no partial text.

🔎 How Does the Formula Work?

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

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