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 fill color
- 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