Excel INDEX MATCH If Cell Contains Text

Introduction to the Excel INDEX Function

The INDEX function returns the cell value of a defined array or a range.

• Syntax:

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

• Arguments:

array: The cell range or a constant array.

row_num: The row number from the required range or array.

[col_num]: The column number from the required range or array.

[area_num]: The selected reference number of all the ranges that  This is optional.

Introduction to the Excel MATCH Function

The MATCH function is used to find the position of a lookup value in an array or a range. It returns a numeric value.

• Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

• Arguments:

lookup_value: The searching value in a lookup array or range.

lookup_array: The lookup array or range of cells where we want to search for the value.

[match_type]: This indicates the type of match for the function to perform. There are three types:

An exact match of the value = 0

The largest value which is equal to or less than the search value = 1

The smallest value which is equal to or greater than the search value = -1

Excel INDEX MATCH If Cell Contains Text: 9 Quick Ways

Method 1 – Use of INDEX MATCH Functions for a Simple Lookup

Case 1.1 – For Vertical Lookup

Consider a dataset of student names with their math marks in vertical position. We are going to look up Rob’s math scores in range B4:C9 and return the value in cell E5.

Steps:

• Select Cell E5.
• Copy the following formula:

• Hit Enter for the result.

➥ Formula Breakdown

➤ MATCH(“Rob”,$B$5:$B$9,0)

This will search for the exact match in range B5:B9.

➤ INDEX($B$5:$C$9,MATCH(“Rob”,$B$5:$B$9,0),2)

This will return the value from the range B5:C9.

Case 1.2 – For Horizontal Lookup

Here we have the same dataset in a horizontal position. We are going to look for Rob’s math marks in range B4:G5 and return the value in cell B8.

Steps:

• Select Cell B8.
• Insert the formula:

• Press Enter to see the result.

➥ Formula Breakdown

➤ MATCH(“Rob”,$C$4:$G$4,0)

This will search for the exact match in range C4:G4.

➤ INDEX($C$4:$G$5,2,MATCH(“Rob”,$C$4:$G$4,0))

This will return the value from the range C4:G5.

Method 2 – Insert the INDEX MATCH Function to Lookup Left

Let’s say we have a dataset (B4:E9) of student names with their English, Math, and Physics marks. We are going to look up Rob’s math marks and return the value in cell G5.

Steps:

• Select Cell G5.
• Copy the formula below:

• Hit Enter to get the result.

➥ Formula Breakdown

➤ MATCH(“Rob”,E5:E9,0)

This will search for the exact match in range E5:E9.

➤ INDEX($B$5:$E$9,MATCH(“Rob”,E5:E9,0),2)

This will return the value from the range B5:E9.

Method 3 – Two Way Lookup with INDEX MATCH Functions If a Cell Contains a Text

Here we have a dataset (B4:E9) of different student names with their different subject marks. We are going to extract all the subject marks of Rob in cell C12:E12 and have listed Rob’s name in a specific lookup cell.

Steps:

• Select Cell C12.
• Insert the following formula:

• Press Enter.
• Use the Fill Handle to the right to autofill the cells.

➥ Formula Breakdown

➤ MATCH($B$12,$B$5:$B$9,0)

This will search for the exact match of Rob in range B5:B9.

➤ MATCH(C$11,$C$4:$E$4,0)

This will search for the exact match of the subject (ENGLISH/MATHS/PHYSICS) in range C4:E4.

➤ INDEX($C$5:$E$9,MATCH($B$12,$B$5:$B$9,0),MATCH(C$11,$C$4:$E$4,0))

This will return the value from the range C5:E9.

Method 4 – Use of INDEX MATCH Functions to Lookup a Value from Multiple Criteria

From the following dataset, we want to extract the Physics marks of ‘Mike Hansen’ from the range B4:D9 in cell D12.

Steps:

• Select Cell D12.
• Paste the following formula into the cell:

• Hit Enter to see the result.

➥ Formula Breakdown

➤ MATCH($B$12&”|”&$C$12,$B$5:$B$9&”|”&$C$5:$C$9,0)

This will combine the lookup values ‘Mike’ & ‘Hansen’ and search for the exact match in a lookup range $B$5:$B$9&”|”&$C$5:$C$9.

➤ INDEX($D$5:$D$9,MATCH($B$12&”|”&$C$12,$B$5:$B$9&”|”&$C$5:$C$9,0))

This will return the value from the range D5:D9.

Method 5 – Use of INDEX, MATCH, and SUM Functions to Get Values Based on Text in a Cell

Let’s get the total subject marks of the student ‘Rob’.

Steps:

• Select Cell C12.
• Insert the following formula:

• Press Enter to see the result.

➥ Formula Breakdown

➤ MATCH($B$12,$B$5:$B$9,0)

This will search for the exact match of cell B12 in range B5:B9.

➤ INDEX($C$5:$E$9,MATCH($B$12,$B$5:$B$9,0),0)

This will return the value from the range C5:E9. Here inside the INDEX function, we will input ‘0’ as a column number. This will return all the values in the row.

➤SUM(INDEX($C$5:$E$9,MATCH($B$12,$B$5:$B$9,0),0))

This will sum up all the returned values from the previous step.

Method 6 – Insert INDEX MATCH Functions with an Asterisk for a Partial Match with Cell Text

Asterisk is an Excel Wildcard Character that represents any number of characters (including none) in a text string. In the bellow dataset (B4:C9) we have all the students’ full names with their math marks, and a list of students’ first names. We are going to find their math marks and input them in range F5:F9.

Steps:

• Select Cell F5.
• Insert this formula:

• Hit Enter and use the Fill Handle to autofill the cells.

➥ Formula Breakdown

➤ MATCH(E5&”*”,$B$5:$B$9,0)

As a lookup value, we will use E5&”*” as the Asterisk returns with the characters starting with the name ‘Bob’ and any number of the characters after it from the text string range B5:B9.

➤ INDEX($C$5:$C$9,MATCH(E5&”*”,$B$5:$B$9,0),1)

This will return the value from the range C5:C9.

➥NOTE: This formula works if there is only one occurrence of matching. In the case of multiple matching occurrences, it will show only the first matching.

Method 7 – Excel INDEX MATCH Functions to Find the Closest Match

Assume we have a dataset (B4:C9) of students’ CGPA. We are going to find the student who has the closest matched CGPA with the required CGPA in cell C12.

Steps:

• Select Cell C12.
• Insert this formula:

• Press Enter to see the result.

➥ Formula Breakdown

➤ MATCH(MIN(ABS(B12-C5:C9)),ABS(B12-$C$5:$C$9),0)

This will search for the exact match of cell B12 in range B5:B9.

➤ MIN(ABS(B12-C5:C9)

This will give the minimum difference between the required CGPA and all other CGPA. To make sure the closest (more or less) value, we will use the ABS function here. Inside the MATCH function, the minimum value will be the lookup value.

➤ ABS(B12-$C$5:$C$9)

This will be the lookup array inside the MATCH function.

➤ MATCH(MIN(ABS(B12-C5:C9)),ABS(B12-$C$5:$C$9),0)

Now the MATCH function will find out the position number of the student’s name from the array who has the closest CGPA.

➤ INDEX($B$5:$B$9,MATCH(MIN(ABS(B12-C5:C9)),ABS(B12-$C$5:$C$9),0))

This will return the name of the student.

Method 8 – Finding an Approximate Match with INDEX & MATCH Functions

Here we have a dataset with all the student’s marks. There is also a grading table beside the main table. We are going to find out each student’s grading in range D5:D9 based on the right one (F5:G10).

Steps:

• Select Cell D5.
• Insert this formula:

• Press Enter and use the Fill Handle to see all the results.

➥ Formula Breakdown

➤ MATCH(C5,$F$6:$F$10,1)

This will search for the exact match of cell C5 in range F6:F10. That means it will go through the marks range and returns the value which will be less than or equal to the lookup value.

➤ INDEX($G$6:$G$10,MATCH(C5,$F$6:$F$10,1),1)

This will return the grade using the position value from the previous step.

Method 9 – Case Sensitive Lookup with INDEX and MATCH Functions If Cells Contains a Text

Let’s say we have a dataset of students’ names with their CGPA. There are two students with the same name. The only difference between them is one is written as ‘lily’ and the other one is ‘Lily’. Now we are going to extract Lily’s CGPA and return the value in cell C12.

Steps:

• Select Cell C12.
• Insert this formula:

• Hit Enter to see the result.

➥ Formula Breakdown

➤ EXACT(B12,B5:B9)

This will find the exact match of the lookup value. It will return TRUE for the exact match and FALSE for no match.

➤ MATCH(TRUE,EXACT(B12,B5:B9),0)

This will find the position of TRUE from the previous step.

➤ INDEX($C$5:$C$9,MATCH(TRUE,EXACT(B12,B5:B9),0),1)

This will return the CGPA using the position value from the previous step.

Practice Workbook

<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel