Search for value in a range
 

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.

Search for value in a range
 

Try it as:
=IF(ISNUMBER(MATCH(A2,B$2:B$30,0)),12,0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Student" wrote:
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.

Search for value in a range
 

Look in the help index for MATCH

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Student" wrote in message
...
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.

Search for value in a range
 

On Wed, 30 Jan 2008 15:16:00 -0800, Student
wrote:

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

Search for value in a range
 

Try this:

=COUNTIF(B2:B30,A2)*12

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

Regards,

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

"Student" wrote in message
...
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.

Search for value in a range
 

On Wed, 30 Jan 2008 19:27:29 -0500, "Ron Coderre"
wrote:

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

Search for value in a range
 

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)


"Ron Rosenfeld" wrote in message
...
On Wed, 30 Jan 2008 19:27:29 -0500, "Ron Coderre"
wrote:

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