## 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,Â “So**”**in**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 the
**Last 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**.

**Download Practice Book**

Download the practice book here.

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