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.

**Table of Contents**hide

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

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.

**⧭**** 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.

**⧭**** 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.

**⧭**** 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)`

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.

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

**How to Use Conditional Formatting Based on VLOOKUP in Excel****Excel Conditional Formatting on Multiple Columns****Conditional Formatting with Formula for Multiple Conditions in Excel****Excel Conditional Formatting Formula with IF**

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

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)`

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

- Here we’ve applied the formula on a single column, that’s why we had to use either the
**Relative Cell References**or the**Mixed Cell References**of the cells**C4**and**D4**. (**C4**or**$C4**and**D4**or**$D4**). - When we apply
**Conditional Formatting**in a single column, we can use either the**Relative Cell Reference**or the**Mixed Cell Reference**of the cells.**See method 1**for details.

**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)`

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.

**⧭**** 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.