Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)

In this article, I’ll show you how you can apply Conditional Formatting over a data set with the INDEX-MATCH functions in Excel. You’ll learn to use Conditional Formatting with INDEX-MATCH with both a fixed lookup value and a variable lookup value, on a single column and on multiple columns.


Download Practice Workbook


4 Easy Ways to Use Conditional Formatting with INDEX-MATCH in Excel

Here we’ve got a data set with the total sales of some products of 10 days of a company called Jupyter Group.

Data Set to Use Conditional Formatting with INDEX-MATCH in Excel

Today our objective is to apply Conditional Formatting on this data set with the INDEX-MATCH functions of Excel.


1. Use Conditional Formatting with INDEX-MATCH with a Fixed Lookup Value in Excel (Over a Single Column)

First of all, let’s try to apply Conditional Formatting with INDEX-MATCH with a fixed lookup value, over a single column in Excel.

Let’s try to highlight the names of the products that had the total sales more than the sales of Jersey.

I am showing you the step-by-step procedure to accomplish this.

Step 1: Selecting the Column and Opening Conditional Formatting:

➤ Select the column on which you want to apply Conditional Formatting. Here I’ve selected the column Product Name (C4:C13).

➤ Then go to the Home > Conditional Formatting>New Rule tool in the Excel Toolbar.

Conditional Formatting Tool to Use Conditional Formatting with INDEX-MATCH in Excel

Step 2: Inserting Formula in the New Format Rule Box:

➤ Click on New Rule. A dialogue box called New Formatting Rule will appear.

➤ Select use a formula to determine which cells to format. Then insert any of the following formulas in the formula box:

=D4>INDEX($B$4:$D$13,MATCH("Jersey",$C$4:$C$13,0),3)

Or

=$D4>INDEX($B$4:$D$13,MATCH("Jersey",$C$4:$C$13,0),3)

Step 3: Selecting the Format:

➤ Then click on Format. A dialogue box called Format Cells will appear.

➤ Choose any format that you want to apply to the cells that fulfill the criteria. I chose the light brown color from the Fill tab.

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

Step 4: The Final Output:

➤ Then click on OK. You’ll be directed back to the New Formatting Rule box.

➤ Again click on OK. You will find the names of the products that had sales greater than the sales of Jersey highlighted in the light brown.

Output to Use Conditional Formatting with INDEX-MATCH in Excel

Explanation of the Formula:

  • MATCH(“Jersey”,$C$4:$C$13,0) searches each row of the column C4:C13, 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$4:$D$13,MATCH(“Jersey”,$C$4:$C$13,0),3) now becomes INDEX($B$4:$D$13,3,3). It returns the value from the 3rd row and 3rd column of table B4:D3. See the INDEX function for details.
  • This is the total sales of Jersey, $830,000.00.
  • $D4>INDEX($B$4:$D$13,MATCH(“Jersey”,$C$4:$C$13,0),3) now becomes $D4>830000. It returns TRUE if the value in cell D4 is greater than 830000, otherwise, it returns FALSE.
  • Now the formula will move to the next cell of the selected column, to cell C5. It will return TRUE if the value in column D5 is greater than 830000, otherwise, it will return FALSE.
  • Similarly, it will move to all the cells from C4:C13 and return TRUE if the value in the respective cell in D4:D13 is greater than 830000, otherwise, it will return FALSE.
  • Now the cells that got TRUE will be highlighted in your desired format, thus 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 D4 (D4 or $D4).
  • When the formula moves down to cell C5, the cell reference in the formula automatically becomes D5 if you use D4 or $D4. But it would have remained D4 if you had used $D$4 or D$4. (Similar to dragging the Fill Handle with a formula).
  • Therefore, while applying Conditional Formatting on a single column, you must use either Relative Cell Reference or the Mixed Cell Reference of the cells of that column.

2. Apply Conditional Formatting with INDEX-MATCH with a Fixed Lookup Value in Excel (Over Multiple Columns)

You can also use Conditional Formatting with INDEX-MATCH with a fixed lookup value over multiple columns of a data set.

Let’s try to highlight the whole rows (Date, Product Name, Total Sales) of the products that had sales greater than the sales of Jersey.

Steps to Apply Conditional Formatting:

The steps are the same as method 1. Just in step 1, select the whole data set in lieu of a single column.

And in step 2, insert the formula with the Mixed Cell Reference.

=$D4>INDEX($B$4:$D$13,MATCH("Jersey",$C$4:$C$13,0),3)

Inserting Formula to Use Conditional Formatting with INDEX-MATCH in Excel

Then choose your desired format from the Format Cells dialogue box.

Then click OK twice. You’ll find the whole rows of the products that had sales greater than the sales of Jersey marked in your desired format.

Conditional Formatting with INDEX-MATCH in Multiple Columns in Excel

Explanation of the Formula:

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

Things to Remember:

  • Here we’ve applied Conditional Formatting on multiple columns. That’s why we used the Mixed Cell Reference of cell D4 ($D4) in the formula.
  • When the formula moves to cell B5 from B4, the cell reference becomes D5 from D4, but when the formula moves to C4 from B4, the cell reference remains constant D4 (Like dragging the Fill Handle).
  • Therefore, while applying Conditional Formatting on multiple columns, you must use the Mixed Cell References of the cells.

Similar Readings:


3. Assign Conditional Formatting with INDEX-MATCH with a Variable Lookup Value in Excel (Over a Single Column)

This time we will apply Conditional Formatting with INDEX-MATCH over a single column, but with a variable lookup value.

Look at this new worksheet. We have the sales record of some products in a data set, along with the target sales of the products in another data set.

Now we’ll highlight the names of the products in the original data set that had sales greater than the respective target sales.

Steps to Apply Conditional Formatting:

The steps are the same as the previous two methods. Just in step 1, select the column Product Name.

Conditional Formatting Option to Use Conditional Formatting with INDEX-MATCH in Excel

And in step 2, use one of the two formulas:

=D4>INDEX($F$4:$G$13,MATCH(C4,$F$4:$F$13,0),2)

Or

=$D4>INDEX($F$4:$G$13,MATCH($C4,$F$4:$F$13,0),2)

Inserting Formula to Use Conditional Formatting with INDEX-MATCH in Excel

Then choose your desired format from the Format Cells dialogue box.

Then click OK twice. You’ll find the names of the products that had sales greater than the target sales marked in your desired format.

Explanation of the Formula:

  • MATCH($C4,$F$4:$F$13,0),2) searches each row of the column F4:F13, and returns the number of the row if it finds the value in cell C4 there (Full Sleeves). See the MATCH function for details.
  • Here it finds “Full Sleeves” in the 1st row. So it returns 1.
  • INDEX($F$4:$G$13,MATCH($C4,$F$4:$F$13,0),2) now becomes INDEX($F$4:$G$13,1,2). It returns the value from the 1st row and 2nd column of table F4:G13. See the INDEX function for details
  • This is the target sales of Full Sleeves, $540,000.00.
  • $D4>INDEX($F$4:$G$13,MATCH($C4,$F$4:$F$13,0),2) now becomes $D4>540000. It returns TRUE if the value in cell D4 is greater than 540000, otherwise, it returns FALSE.
  • Now the formula will move to the next cell of the selected column, to cell C5. It will return TRUE if the value in column D5 is greater than 900000 (Target Sales of Half Sleeves), otherwise, it will return FALSE.
  • Similarly, it will move to all the cells from C4:C13 and return TRUE if the respective value in D4:D13 is greater than the target sales, otherwise, it will return FALSE.
  • Now the cells that got TRUE will be highlighted in your desired format, thus the products that had sales greater than the target sales will be highlighted.

Things to Remember:


4. Use Conditional Formatting with INDEX-MATCH with a Variable Lookup Value in Excel (Over Multiple Columns)

Finally, let’s try to highlight the whole rows of the products that could fulfill their target sales.

Steps to Apply Conditional Formatting:

The steps are the same as the previous 3 methods. Just in step 1, select the whole data set.

And in step 2, insert the formula with the Mixed Cell Reference.

=$D4>INDEX($F$4:$G$13,MATCH($C4,$F$4:$F$13,0),2)

Inserting Formula to Use Conditional Formatting with INDEX-MATCH in Excel

Then 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 to Use Conditional Formatting with INDEX-MATCH in Excel

Explanation of the Formula:

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. See method 2 for details.


Conclusion

Using these methods, you can use Conditional Formatting with INDEX-MATCH with both fixed lookup value and variable lookup value in Excel. Do you have any questions? Feel free to ask us.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo