Say YES if it says "Discount" in a list

[GrumpyChi]

Hi,

I have a list of data with multiple lines

1001A Full
1001B Discount
1002C Full
1001A Discount
1003D Full

Next to each cell I want the formula to return whether the reference has a discount anywhere in the list. EG. The very first line which says '1001A Full' I want to return it to say "YES" because we can see on row 4 it has a Discount. If it just says "Full" and elsewhere it has no discount, then it can just say "NO". I cannot seem to figure out a way without it being extremely long winded.

Thank you

 

[Misca]

Try something like:

=IF(SEARCH("discount",TEXTJOIN("",TRUE,ListRange)),"Yes","No")

Replace the ListRange with your actual range.

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	1001A Full	Yes
3	1001B Discount	Yes
4	1002C Full	No
5	1001A Discount	Yes
6	1003D Full	No
Main

Cell Formulas
Range	Formula
B2:B6	B2	=IF(ISNUMBER(MATCH(LEFT(A2,5)&" discount",$A$2:$A$6,0)),"Yes","No")