If Function Help, Please!

[Guest]

Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I need
to analyze. I want to write an IF statement that will check if the value of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I will be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


....C_Guy

 

[kraljb]

=IF(A8=A3:F7,1,0)

Try this instead...

=IF(OR(TYPE(MATCH(A8,A3:A7,0))=1,TYPE(MATCH(A8,B3:B7,0))=1,TYPE(MATCH(A8,C3:C7,0))=1,TYPE(MATCH(A8,D37,0))=1,TYPE(MATCH(A8,E3:E7,0))=1,TYPE(MATCH(A8,F3:F7,0))=1),1,0)

Rather ugly, but from my experience match does not work across a 2-
range. Maybe there is a better way to search the range for the valu
(either that or change the range into a single column or row... Hop
that puts you in the right direction at least..

 

[Morrigan]

Read up on MATCH() and LOOKUP(), they may be what you are looking for.


Hope this helps.

 

[Bill Kuunders]

=COUNTIF(A3:F7,A8)

 

[Guest]

Hi Bill;

I considered 'Countif' but in the case that the value appears twice the
function will return a '2'. I want the function to only return only
TRUE/FALSE or 0/1 regardless of how many times the value appears.

....C_Guy

 

[Bill Kuunders]

not a problem

=IF(COUNTIF(A3:F7,A8)>0,"true","false")

( :>)
Greetings from New Zealand
Bill K

 

[JE McGimpsey]

One way:

=--(COUNTIF(A3:F7,A8)>0)

 

[Guest]

not a problem

=IF(COUNTIF(A3:F7,A8)>0,"true","false")

( :>)
Greetings from New Zealand
Bill K