How to Apply Conditional Formatting with INDEX-MATCH in Excel: 4 Methods

Method 1 – Using Conditional Formatting with INDEX-MATCH with a Fixed Lookup Value in Excel (Over a Single Column)

Steps:

  • Select the column on which you want to apply Conditional Formatting.
  • We selected the column Product Name (C5:C14).
  • Go to the Home > Conditional Formatting>New Rule tool in the Excel Toolbar.

selecting conditional formatting option

  • You will see a dialogue box called New Formatting Rule appeared.
  • Select use a formula to determine which cells to format.
  • Insert any of the following formulas in the formula box:
=D5>INDEX($B$5:$D$14,MATCH("Jersey",$C$5:$C$14,0),3)

Or

=$D5>INDEX($B$5:$D$14,MATCH("Jersey",$C$5:$C$14,0),3)

setting new formatting rule box

  • Click on Format. A dialogue box called Format Cells will appear.
  • Choose any format you want to apply to the cells that fulfill the criteria. We chose the light brown color from the Fill tab.

Choosing Format to Use Conditional Formatting with INDEX-MATCH in Excel

  • Click on OK. You’ll be directed back to the New Formatting Rule box.
  • You will see a preview in the New Formatting Rule dialog box. Click OK to close the box.

preview in new formatting rule box

  • you will find the names of the products with sales greater than Jersey’s highlighted in light brown.

Output to Use Conditional Formatting with INDEX-MATCH in Excel

Formula Breakdown:

  • MATCH(“Jersey”,$C$4:$C$13,0) searches each row of the column C5:C14, and returns the number of the row if it finds Jersey. See the MATCH function for details. Here it finds Jersey in the 3rd row. So it returns 3.
  • INDEX($B$5:$D$14,MATCH(“Jersey”,$C$5:$C$14,0),3) now becomes INDEX($B$5:$D$14,3,3). It returns the value from the 3rd row and 3rd column of table B5:D14. See the INDEX function for details. This is the total sales of Jersey, $830,000.00.
  • $D5>INDEX($B$5:$D$14,MATCH(“Jersey”,$C$5:$C$14,0),3) now becomes $D5>830000. It returns TRUE if the value in cell D5 is greater than 830000, otherwise, it returns FALSE.
  • Now, the formula will move to the next cell of the selected column, to cell C6. It will return TRUE if the value in column D6 is greater than 830000, otherwise, it will return FALSE.
  • Move to all the cells from C5:C14 and return TRUE if the value in the respective cell in D5:D14 is greater than 830000; otherwise, it will return FALSE.
  • The cells that got TRUE will be highlighted in your desired format, the products that had sales greater than the sales of Jersey will be highlighted.

Things to Remember:

  • Here we’ve applied the formula on a single column, that’s why we had to use either the Relative Cell Reference or the Mixed Cell Reference of the cell D5 (D5 or $D5).
  • When the formula moves down to cell D6, the cell reference in the formula automatically becomes D6 if you use D5 or $D5. But it would have remained D5 if you had used $D$5 or D$5. (Similar to dragging the Fill Handle with a formula).
  • Therefore, while applying Conditional Formatting on a single column, you must use either the Relative Cell Reference or the Mixed Cell Reference of the cells of that column.

Method 2 – Applying Conditional Formatting with INDEX-MATCH with a Fixed Lookup Value in Excel (Over Multiple Columns)

Steps:

  • The steps are the same as the initial steps of Method 1. Just select the whole data set in lieu of a single column.

selecting conditional formatting for multiple columns

  • Insert the formula with the Mixed Cell Reference.
=$D5>INDEX($B$5:$D$14,MATCH("Jersey",$C$5:$C$14,0),3)

setting new formatting rule box

  • Choose your desired format from the Format Cells dialogue box.
  • Click OK twice. You’ll find the rows with products whose sales were greater than Jersey’s marked in your desired format.

Output to Use Conditional Formatting with INDEX-MATCH in Excel

Formula Breakdown:

The explanation of the formula is the same as the earlier method. See it for details.

Things to Remember:

  • We applied Conditional Formatting on multiple columns. We used the Mixed Cell Reference of cell D5 ($D5) in the formula.
  • When the formula moves to cell B6 from B5, the cell reference becomes D6 from D5, but when the formula moves to C5 from B5, the cell reference remains constant at D5 (Like dragging the Fill Handle).
  • While applying Conditional Formatting on multiple columns, you must use the Mixed Cell References of the cells.

Method 3 – Assigning Conditional Formatting with INDEX-MATCH with a Variable Lookup Value in Excel (Over a Single Column)

Steps:

The steps are the same as the previous methods. Select the column Product Name and select New Rule from Conditional Formatting.

selecting conditional formatting new rule

  • Use one of the two formulas:
=D5>INDEX($F$5:$G$14,MATCH(C5,$F$5:$F$14,0),2)

Or

=$D5>INDEX($F$5:$G$14,MATCH($C5,$F$5:$F$14,0),2)

setting new formatting rule box

  • Choose your desired format from the Format Cells dialogue box.
  • Click OK twice. The names of the products with sales greater than the target sales will be marked in your desired format.

output of index match conditional formatting

Formula Breakdown:

  • MATCH($C5,$F$5:$F$14,0),2) searches each row of the column F5:F14, and returns the number of the row if it finds the value in cell C5 there (Full Sleeves). See the MATCH function for details. It finds Full Sleeves in the 1st row. So it returns 1.
  • INDEX($F$5:$G$14,MATCH($C5,$F$5:$F$14,0),2) now becomes INDEX($F$5:$G$14,1,2). It returns the value from the 1st row and 2nd column of table F5:G14. See the INDEX function for details. This is the target sales of Full Sleeves, $540,000.00.
  • $D5>INDEX($F$5:$G$14,MATCH($C5,$F$5:$F$14,0),2) now becomes $D5>540000. It returns TRUE if the value in cell D5 is greater than 540000, it returns FALSE.
  • The formula will move to the next cell of the selected column, to cell C6. It will return TRUE if the value in column D6 is greater than 900000 (Target Sales of Half Sleeves); otherwise, it will return FALSE.
  • It will move to all the cells from C5:C14 and return TRUE if the respective value in D5:D14 is greater than the target sales, it will return FALSE.
  • The cells that got TRUE will be highlighted in your desired format, the products that had sales greater than the target sales will be highlighted.

Things to Remember:

  • We applied the formula on a single column, so we had to use either the Relative Cell References or the Mixed Cell References of the cells C5 and D5. (C5 or $C5 and D5 or $D5).
  • When we apply Conditional Formatting in a single column, we can use either the Relative Cell Reference or the Mixed Cell Reference in the cells.

Method 4 – Using Conditional Formatting with INDEX-MATCH with a Variable Lookup Value in Excel (Over Multiple Columns)

Steps:

  • The steps are the same as Method 1. Just select the whole data set.

selecting conditional formatting new rule

  • Insert the formula with the Mixed Cell Reference.
=$D5>INDEX($F$5:$G$14,MATCH($C5,$F$5:$F$14,0),2)

setting new formatting rule box

  • Select your desired format and click OK twice.
  • You’ll find the rows of the products that fulfilled the target sales highlighted in your desired format.

output of index match conditional formatting

Formula Breakdown:

The explanation of the formula is the same as Method 3. See it for details.

Things to Remember:

While applying Conditional Formatting on multiple columns, you must use the Mixed Cell Reference of the cells.


Download Practice Workbook

you can download the practice workbook from here.


Related Readings

<< Go Back to Conditional Formatting Formula | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo