Excel Magic Trick 1509: Conditional Format Array Formula to Highlight Row With 2 Lookup Values

Download Files:

In this video see how to color a row with conditional formatting using an Array Formula & the MATCH Function that will lookup Two Lookup Values in a corresponding Table.

[GlenSlagle]

This was a live-saver! I do inventory and I have an Excel map of all the warehouse buildings and racks. I wanted to input an item number, have it search inventory records, and highlight on the map everywhere in the building that item is stored. Took some finagling but it works like a charm. I had to use a helper TRUE/FALSE column, but maybe I can integrate it.

[universalproblemsolver]

Fully aware about CSE in cells. Completely unaware of the "don't use CSE in Conditional Formatting"... until now.
Thank you for upping my Excelfu.

[MalinaC]

wow! I was going to write a formula in condition formatting that highlight duplicates based on 2 columns. I haven't even started yet when you uploaded your film. And now I know I will use your brilliant solution in mine. Thank's Mike!

[aguerojg]

What a great video, Mike, I have never imagined to concatenate cells with Match function. Thank you so much!!

[DIGITAL_COOKING]

Your welcome Mike, thanks for your great channel I find the answers to a lot of problems I think I should call you a teacher

[deepaksharma]

Nice video bro..I have been watching your videos last 2.6 year...You always rock

[johnborg]

Thanks Mike. Just Love Array Formulas. :)

[rrrprogram]

That was revision of funwith excel... Good one mike

[NoShadowOfDoubt]

Great trick...hopefully it works good and quick in big data

[ismailismaili]

I really was looking for this Mr. Mike thank u so much i really appreciate

[DIGITAL_COOKING]

Ha ha ha, for the first time I'm the number "one" viewing your video

[ituryu]

Hi, thanks for the video. I have a question, can you conditionally format merged rows dependent on the text values of another cell?

[daeh]

It is fun watching that subscribed number growing. I wonder what the rate of growth is?

[kongtat]

I would like highlight the NON-Match cell, how to do this ? Thank you

[sachinrv]

Very good video. Can we join MATCH with IFERROR here? But anyway if conditional formatting is taking care without using IFERROR, then nothing like it. Thanks for uploading :)

[sandeepkothari]

Mike, I was wondering how VBA macro recorder handles the keystrokes of C+S+E & curly brackets?

[masterof]

Is there a way to use the similar formula-way to do the icon sets? For example, based on the quantity of products being sold, when a particular product been sold more than x numbers, show Red light icon, between x and Y numbers, show Yellow light icon, and less than Y number, show Green light icon...etc. or based on the product selling price ...etc.

[irfankhan-vbxp]

How can count background specfic text mean if i put in yellow colour 20. All yellow in 20 then green 50 after i want count 50 with green color how many and 20 yellow how many i think you understand what i want thanks so much

[sarasamin]

Hello, subscribed to these lessons a few years ago.I got emails on a regularly for a while the then all of a sudden It stopped and I've not received any email ever since.

[jasonm]

Strange how it interprets those the way it does. I thought you were going to have to use ISNUMBER.