Using conditional IF statement

Posted by Amar Khalifeh on August 02, 2000 12:54 PM

How do I use the IF function, or any function, to return 1/0 (true/false) in this situation:
=if(b3=x100:x300,1,0)
where I'm trying to match the value in b3 to any of the values in the range x100:x300. The function is to return a "1" if there is at least one match, and "0" otherwise. Any hints/ideas will be appreciated.

Posted by Joe Danna on September 30, 2000 5:00 AM

SUMIF on 1 colum totaling another....

How can I total one column by using another column with all words that begin with an *?

I need a simple formula. I have tried many.

Thanks,

Joe Danna

Posted by Ivan Moala on August 03, 0100 3:48 AM

or if you don't want the #N/A to appear due to
then you can use the array formula
=IF(OR(B1=X100:X300),1,0)

enter this using Ctrl + shift + enter

See Mrexcels tips on CSE formulas

Ivan

Posted by Tim Francis-Wright on August 02, 0100 2:53 PM

You could try
=IF(ISNA(VLOOKUP(b3,x100:x300,1,FALSE)),0,1)

This checks if the VLOOKUP function returns
#N/A. If so, it returns 0, else it returns 1.