Excel Magic Trick 1383: Conditional Format Row w OR Logical Test with Multiple Partial Text Criteria

Download Files:

Start File and Finished File:

See how to Conditionally Format each row in a Data Set that has a Job Title that contains any text item from within a list. Learn about “Contains Criteria” in an OR Logical Test:
1. (00:11) Introduction
2. (02:53) OR, ISNUMBER and SEARCH functions in an Logical Array Formula that requires Ctrl + Shift + Enter (in the cells). Learn how Array Formulas that require Ctrl + Shift + Enter will work in the Conditional Formatting Dialog Box.
3. (08:45) Convert Contains Criteria list to an Excel Table to allow the list of criteria to expand or contract.
4. (10:13) LOOKUP and SEARCH functions in an Logical Array Formula that does NOT requires Ctrl + Shift + Enter (in the cells). Learn how the Big Number 2^15 can be used in an Approximate Match Lookup Formula when searching for text in a cell.
5. (13:35) Summary
6. Not in video, but in the downloadable Workbook compare and time the LOOKUP Formula using either SEARCH or MATCH functions.

Alternative Title: Excel Magic Trick 1383: Conditional Format Row w OR Logical Test Multiple Contains (Partial Text) Criteria
Match Job Title to List of Key Words

[LeilaGharani]

I really like the Lookup version. Thank you.

[md.soberali]

Helpful lesson, it save my time. Thank you

[kbell]

Great lesson! Informative and spot on conditional formatting instruction dealing with multiple values in a text string—saved me a ton of time today!

[Al-Ahdal]

Excel is really fun with Mike (Excel Guru).

[samkab]

thank you. i was looking for this one for quite a while . very well explained as always .

[ryanemerson]

Are you able to use conditional formatting to highlight a specific day inside a cell with a date. Ex. If a column has all dates (long form) of several years and I need to highlight all the Mondays. I know excel reads dates as numbers so I’m stuck.

[topgundk]

I know this is an old one...
When i change my lookup values to be an table, my test formula in the spreadsheets, works fine. Get the TRUE there, and the formula have extended its range by a cell.
BUT in my Conditional Format formula, it does not expand the range..

I have tested creating tables first, so i refer to the table name, instead of a range. Well, that also works fine in my test formula, but not at all in Conditional Format formula. It does not like table references in there :)

Any suggestions ?

[bastenmarsman]

This is great. Thank you! Now I have one question left.
Is it possible to conditional format that if I type one number (1-15) in cel A1, it automaticly fill a certain text in cel B2 and B3? Is that even posible?

[galoshel]

how to highlight the cells which has a number and keep blank the empty ones?

[mohammedhosen]

Many many thanks for this beautiful video!
Q: Let's suppose all what I need is the outcome of the evaluation itself - i.e. I need an array of TRUES and FALSES. Any idea how can I leave it ?

[ronaldotabugan]

hi sir,
your great, and your videos are amazing.

but can i ask if you have some video that is close to what i am trying to achieve?
and where can i post or send you image to describe it properly?

thank you!

[akram]

It's amazing lesson, thanks alot.

[davida.taylor]

man I wish I had known of this 2 years ago!

[suja]

But when the cells are blank it gives a true value, what should we include in the formula for that please?

[MrHorzel]

Thank you again for this fantastic video, I noticed that when you delete a record in the criteria table everything gets selected, which makes sense as you tell excel to find: " " (an empty string), which it will find in every single cell of course. (correct me if I'm wrong). I noticed though this tiny handle on the right hand bottom corner of the table, if you slide this up you adjust the content accordingly. Is it possible though to select random items from the table, or are we diving into VBA then?

[m.n.]

Thank you sir for nice and clear video, I have a question pleas. I make a table that contains in culomn A doctors names and the headers in row1 is the month date. Inside the table all the cells has data validation with departments in hospital to make a monthly rota shift for the doctors. The list of the data valedation contains (main department, long shift, short shift, emergency, operation room, DAY OFF).
I want to make a conditional FORMATING THAT IF I CHOOSE "long shift" from the data validation automatically gives this doctor "DAY OFF" for the next day and color the cell of "DAY OFF" in red.
How can I do that.( For example if I choose in C5 "long shift" automatically mak D5 "DAY OFF" with red background in B5.) thank you.

[manjanetrovicmi]

I need a template to track my help desk tickets. They will be checked three times a week, Monday, Wednesday and Friday.

I need a way to have the dates move forward, could You please help???

[aliasgar]

Sir do you have any video for Dashboard speedometer??

[jegane]

;-))) yeah it me..

I want to conditionally format a cell, if it "does not contain" the text/number from the criteria list (new entry). i used vlookup. and it is not completely useful if a cell contains >1 text in it. can you help it out?

thank you :)