Using IF to find a value in a list

[beckery1]

I am trying to use the IF command to return a certain response if the value is contained in a list. For example if I have a list containing the numbers 1, 2, 3, and 4 and then I have a cell with a "5" in it. I want to create a formula that says look at the cell containing the 5 and if it is in my list return a "1" or else return a "0". I have tried many different approaches but have not been successful. Any ideas? Thanks.

 

[texasalynn]

post some of the things you have tried

texasalynn

 

[beckery1]

If the test value is in A1 and the list in A2:A5, I would type in B1 the following:

=IF(A1=A2:A5,"1","0")

I have tried the above formula with hitting CNTRL+SHFT+ENTR to make it an array formula but the formula then only work for the first value in the list. It is not recognizing any of the other values.

 

[Scott Huish]

If you don't actually want to return a text 0 or 1, but the actual numbers 0 and 1, try this:

=--(ISNUMBER(MATCH(A1,A2:A5,0)))

 

[beckery1]

OK that worked, but what if I want it to return text? Thanks.

 

[Scott Huish]

=IF(ISNUMBER(MATCH(A1,A2:A5,0)),"1","0")

 

[beckery1]

Thanks.

 

[PA HS Teacher]

Have you tried the following?
=-ISNA(MATCH(A1,A2:A5,0))+1

 

[beckery1]

Thanks, I'll try that too.

 

[Aladin Akyurek]

=IF(ISNUMBER(MATCH(A1,A2:A5,0)),"1","0")

How about & instead of IF...

=--(ISNUMBER(MATCH(A1,A2:A5,0)))&""