How to Conditional Formatting the Lookup Result in Excel Data Table (Index Match)

Find out how you can highlight values inside your data set based on a condition. In this example, we are going to conditionally format the result of our lookup formula (I use INDEX Match here) to conditionally format that same value that we find inside the original data set.

The tricky part is to get the conditional formatting formula correct. Just remember, one reason why your conditional formatting formulas might not work can be due to the wrong usage of the dollar sign for fixing the cell references (i.e. absolute, versus relative referencing - and sometimes you need a mix of the two).

Links to related videos:

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[jessebacke]

I thank God, because my best purchase of purchases was not published on Black Friday, but rather the purchase of your courses, you are simply intelligent, dynamic, objective and creative. Thanks LGharani !

[davidferrick]

No one better with explaining Excel tricks than you. Thanks.

[signalmas]

I'm so addicted to your explanations. I could probably find a similar solution if given the problem of some of your videos, but the way you explain and watching how clean you work makes the videos so enjoyable...to the level that prefer watching you on Friday night instead of cute kitties videos after an exhausting work week. The videos on Charts are novelty for me, and I rewatch them when find similar application at work. You are great, thank you!

[ismailismaili]

it was so tricky also thank you because you use Mr. Mike approach to fix that problem and that let me know more about how to use conditional formatting in an efficient way. thank you Ms. Leila, Mr. Mike and Mr. Excel.

[gopalakrishnarao]

Thank you Madam, every time with new innovative ideas, you are publishing for the benefit of Excel users/addicts/learners. A BIG SALUTE to my beloved and beautiful Teacher/Guru and Excel Pundit. Doing a great job indeed. I pray almighty to bestow good health to my beloved teacher. Awaiting some more videos with great tricks/tips.

[elgennacorda]

I love Mike from ExcelIsFun as well, a great teacher. So happy to hear that you knew each other, love you both 🤗💛

[dimitridiscovers]

I think that there may be a much easier way to get the conditional formatting on the values of the table; set the conditional formatting for the first cell in the table (B7) relative to the result cell ($d$3) and color it appropriately (green = exact value, for example.) Then you can copy / paste special >> format to all of the other cells in the table. any cell(s) in the table that is equal to the result cell will be colored green.


thanks for the use of the INDEX and MATCH functions, good stuff!

[theturnip]

brilliant - you always explain so clearly in you videos, thanks so much...just what I needed!

[BillSzysz]

Hi Leila :-). Thanks for perfect explanation!
I had one case in the past where Your formula would not be correct but it concerned case sensitive situation (for example "Level" and "level" are not the same).
Of course we can do the job without any function (operators and $ signs only)
=$A7&B$6=$B$3&$C$3
or if the case sensitive
=EXACT($A7&B$6, $B$3&$C$3)
Regards :-)

[chrisbroomfield]

You solved my problem, such great help thanks.

[donnaassenza]

You are brilliant. Your explanations clear & detailed. Thank you

[nonopanzu]

Hello Leila,
Could you please help me how to conditional formatting cells with Right function?.
I use this formula and work well « =COUNTIF($C$3:$C$8;B3)>0 » to conditional formatting my cells. But now I want to lookup not the entire data in the cells but only the 3 last digit of the data in the cells. I couldn't found the right way to use "Right" function in a lookup formula.
Thanks in advance...✌👍

[venkatiyer]

It's me again to thank you ... though i have seen this video at the neck of the moment I couldn't remember the way to conditionally format the given number ... i referred to you as my excel dictionary and yes I got my answer ... thanks a lot again.

[Xaneke]

As always, mind blowing. Many thanks. Can we use conditional formatting to highlight changes and track the difference while comparing two sheets?

[douglaszulu]

Interesting look at highlighting intersection

[ทวีเกวียนสูงเนิน]

I love your video so much it give me improve the job. thank you ❤❤❤😊.

[aaazyyy]

Came here for something else, learnt something else, found real solution somewhere else.
Hope I had an IQ similar to these excel gurus.

[flaviobendl]

Great ! Excelent explanation, Thank you

[malaparte]

Hola Leila !!! Gracias por comprtir tus conocimientos siempre claros y profundos, Soy tu Fan !!!!

[associateengineer]

Hi hope you'll be fine, I have a question, I wanna to highlight (Monday, Tuesday, Wednesday, Thursday & Friday, Except, Saturday and Sunday) of Current Month of Current Week.