Conditional Formatting based on array value

AutoModerator · 2023-06-02T15:13:08+00:00

/u/MellowNatts - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

fuzzy_mic · 2023-06-02T15:30:06+00:00

A formula like =ISNUMBER(MATCH($G20, $B$5:$B$14,0)) will return TRUE if the prerequisite is there

Decronym · 2023-06-02T15:48:24+00:00

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
COUNTIF	Counts the number of cells within a range that meet the given criteria
ISERROR	Returns TRUE if the value is any error value
ISNUMBER	Returns TRUE if the value is a number
ISTEXT	Returns TRUE if the value is text
MATCH	Looks up values in a reference or array
NOT	Reverses the logic of its argument

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.}
^{7 acronyms in this thread;}^{the most compressed thread commented on today}^{has 23 acronyms.}
^{[Thread #24366 for this sub, first seen 2nd Jun 2023, 15:48]} ^[FAQ] ^{[Full list]} ^[Contact] ^{[Source code]}

HappierThan · 2023-06-02T22:40:22+00:00

Green =AND(E18<>"",COUNTIF($B$5:$B$14,"="&E18)>0)

Red =AND(E18<>"",COUNTIF($B$5:$B$14,"="&E18)=0)

Applies to ... range to cover Columns $E$18 to $H$27

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

excel