Values in a range

[mouseman5]

Is there a straightforward way to check a range for the presence of
specified value that doesn't return N/A if the value isn't present

 

[Bob Phillips]

=IF(ISNA(MATCH(A1,B1:B10,0)),"",MATCH(A1,B1:B10,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
one way:
=IF(COUNTIF(A1:A100,3)>0,"3 is in the range","3 is not in the range")
or
=IF(ISNA(MATCH(3,A1:A100,0)),"3 is not in the range","3 is in the
range")

 

[RagDyeR]

Combine an IF function with the Lookup function, where the IF can return a
null ("") if an error is returned.

For example:

=IF(ISNA(MATCH(D1,A1:A10,0)),"",VLOOKUP(D1,A1:B10,2,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Is there a straightforward way to check a range for the presence of a
specified value that doesn't return N/A if the value isn't present?

 

[mouseman5]

Thanks to you bot

 

[Alan Beban]

=COUNTIF(A1:A100, sought_value)

will return 0 (if not there) or a positive integer.

Alan Beban

 

[Dave Peterson]

One more version of the =countif() suggestion:

=countif(b1:x99,"*myvalue*")
or
=countif(b1:x99,"*" & a1 & "*")

it'll look for that value anywhere in the cell.

 

[Aladin Akyurek]

=--ISNUMBER(MATCH(Value,Range,0))

would be a fast way doing it. 1 as result means "present", 0 "not present".