Formula to find if a number exists in a range of cells?

[LaurenJez]

Hello. I am using the below formula to find if a number (1 to 9) exists in a cell. The formula works, but what I really want is a formula that checks if a number (1 to 9) exists in a range of cells. Can anyone help with this?

Current formula that works: =IF(COUNT(FIND({1,2,3,4,5,6,7,8,9},O2))>0, "Yes", "No")

What I want but is not working: =IF(COUNT(FIND({1,2,3,4,5,6,7,8,9},O2:AB2))>0, "Yes", "No")

 

[Fluff]

How about

=IF(COUNT(FIND({1,2,3,4,5,6,7,8,9},CONCAT(O2:AB2)))>0, "Yes", "No")

 

[MDCurry]

This formula should count the number of cells in the range from 1 to 9
=COUNTIFS(O2:AB2,">=1",O2:AB2,"<=9")

...and if you want Yes or No to return when the count is > 0 then use this:
=IF(COUNTIFS(O2:AB2,">=1",O2:AB2,"<=9")>0,"Yes","No")

 

[LaurenJez]

How about

=IF(COUNT(FIND({1,2,3,4,5,6,7,8,9},CONCAT(O2:AB2)))>0, "Yes", "No")

Unfortunately, that is returning results back all with "No"

 

[Fluff]

Not for me it doesn't

Fluff.xlsm
	N	O	P	Q	R	S	T	U	V	W
1
2	Yes									254A
Main

Cell Formulas
Range	Formula
N2	N2	=IF(COUNT(FIND({1,2,3,4,5,6,7,8,9},CONCAT(O2:AB2)))>0, "Yes", "No")

 

[LaurenJez]

Not for me it doesn't

Fluff.xlsm
	N	O	P	Q	R	S	T	U	V	W
1
2	Yes									254A
Main

Cell Formulas
Range	Formula
N2	N2	=IF(COUNT(FIND({1,2,3,4,5,6,7,8,9},CONCAT(O2:AB2)))>0, "Yes", "No")

Here is what's happening for me -- let me know your thoughts what I'm doing wrong:

 

[Fluff]

Are you using 365 as per your profile, or are you using an older version?

 

[LaurenJez]

Are you using 365 as per your profile, or are you using an older version?

I'm using Excel 2016

 

[Fluff]

In that case why does your profile say 365, maybe you need to change it?

Try confirming the formula with Ctrl Shift Enter, rather than just Enter.

 

[Tetra201]

@ LaurenJez: Your original range formula from Post #1 needs a slight modification to work (and being confirmed using Ctrl Shift Enter, not just Enter)

=IF(COUNT(FIND({1;2;3;4;5;6;7;8;9},O2:AB2))>0,"Yes","No")