Search for value in a range

[Student]

Is there a function which can search for a value in a range and return a
"true" or "false" if it finds it.

eg. =if(A2=B2:B30,12,0) So if the value in cell A2 is found in the range
B2:B30 then 12, if not then 0.

Obviously this particular formula doesn't work otherwise I wouldn't be
asking. How can I change it so it does what I'm try to make it do.

 

[Max]

Try it as:
=IF(ISNUMBER(MATCH(A2,B$2:B$30,0)),12,0)

 

[Don Guillett]

Look in the help index for MATCH

 

[Ron Rosenfeld]

Is there a function which can search for a value in a range and return a
"true" or "false" if it finds it.

eg. =if(A2=B2:B30,12,0) So if the value in cell A2 is found in the range
B2:B30 then 12, if not then 0.

Obviously this particular formula doesn't work otherwise I wouldn't be
asking. How can I change it so it does what I'm try to make it do.

=IF(OR(A2=B2:B30),12,0)

entered as an **array-formula** with <ctrl><shift><enter>. Excel will places
braces {...} around the formula if you do it correctly.

OR:

=SUMPRODUCT(OR(A2=B2:B30)*12)

entered normally.
--ron

 

[Ron Coderre]

Try this:

=COUNTIF(B2:B30,A2)*12

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Rosenfeld]

Try this:

=COUNTIF(B2:B30,A2)*12

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Of course, if there are two matches for A2, this would return 24 and not 12.
--ron

 

[Ron Coderre]

Hence the

Is that something you can work with?

If there are multiple matches of A2 in B2:B30
then perhaps this:
=IF(COUNTIF(B2:B30,A2),12,0)

That formula is durable against
a blank A2 and blanks within B2:B30.
(assuming that wouldn't be a legitimate match)

Thanks for you comments, Ron.
--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)