If Function Help, Please!

C

C_Guy

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
 
K

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,D3:D7,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..
 
M

Morrigan

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


Hope this helps.
 
C

C_Guy

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
 
B

Bill Kuunders

not a problem

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

( :>)
Greetings from New Zealand
Bill K
 
Top