Welcome to Excel Jet Consult. In this blog post, we will learn how to use VLOOKUP & INDEX/MATCH Inside Conditional Formatting. Let’s get started

**Video**

You can watch the video of this tutorial below. Do subscribe to my YouTube channel for more videos

**Sample Data**

In the caption below, we have Date, Items, States and Actual from columns A to D respectively. In columns G and H, we have each of the unique States with their corresponding **Target. **What we want to do essentially is to highlight the entire rows using conditional formatting based on the specified criteria. For example, the **Target **for **Washington **is $20,000 but the **Actual **in row 2 (cell D2) is $5120 which is below the target which means the row will not be formatted. Any **Washington **row with Actual equal to or greater than $20,000 will be formatted. The same applies to other states respectively

**VLOOKUP Solution**

- Select the range: A2:D27 using
**CTRL + SHIFT + Right**and**Down Arrow keys** - Press CTRL + Backspace
- In the
**Styles**group of the**Home**tab, select**Conditional Formatting** - Select
**New Rule**and**use a formula to determine which cells to format**respectively - In the
**format values where this formula is true**box, enter the formula: =$D2>=VLOOKUP($C2,$G$2:$H$6,2,0)

Note, we applied absolute cell reference to lock down column D while row 2 is relative. we also applied absolute cell reference to column C while row 2 is relative. This is necessary because our goal is to highlight **entire row** hence, the row is **relative. **Furthermore, we applied absolute cell reference to the VLOOKUP formula table_array input value which is $G$2:$H$6.

- Click on Format
- Select
**Fill**tab - Then, we applied
**Yellow**fill color - Click
**OK**. See the caption below of the the applied steps

- Click OK

In the caption below, we can see that all the **Actual **that is greater than or equal to the **Target **for each of the **States **were highlighted

For example, in row 2, the **Actual **for Texas is $41,891 which exceeded the **Target ($30,000) **hence, the row was highlighted

**INDEX/MATCH Solution**

- Select the range: A2:D27 using
**CTRL + SHIFT + Right**and**Down Arrow keys** - Press CTRL + Backspace
- In the
**Styles**group of the**Home**tab, select**Conditional Formatting** - Select
**New Rule**and**use a formula to determine which cells to format**respectively - In the
**format values where this formula is true**box, enter the formula: =$D2>=INDEX($H$2:$H$6,MATCH($C2,$G$2:$G$6,0)) - Click on
**Format** - Select
**Fill**tab - Then, we applied
**Red** - Click
**OK**. See the caption below of the the applied steps

- Click OK

From the caption above, we achieved the same result with the VLOOKUP solution

**Conclusion**

We have seen how to use VLOOKUP and INDEX/MATCH to highlight entire row of data based on the specified criteria. This enunciated the fact that there are many ways to perform an operation in Excel and ultimately achieve the same outcome! See you in the next tutorial