VLOOKUP & INDEX/MATCH Inside Conditional Formatting

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

 

Posted in Blog and tagged , , .

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *