IF Function with a Range of Cells

[KuraiChikara]

Hello,
I'm trying to get an IF function to include a range of cells and I cant seem to get it to work.

First without a range:
In cell A1 I have "Sup 1"
In cells B1-B5 I have Sup 3, Sup 4, Sup 5: Sup 6 and Sup 1

In Cell C1 where the formula is, I have the below formula then drag it down:
=IF(A$1=B1,"TRUE","FALSE")

This will work, Cell C5 will return a TRUE statement because Sup 1 is in B5.

What I'm looking to do is to essentially return the True statement while looking in the range but I cant figure it out. Doing the same formula but including the range of B1-B5:

=IF(A1=B1:B5,"TRUE","FALSE")

In my setup, B5 contains Sup 1 which is in the LOGICAL TEST but it returns False and I dont know why.

 

[FDibbins]

1st, you don't really need to use the IF statement the way you did the 1st example.
Instead of
=IF(A$1=B1,"TRUE","FALSE")
try
=A$1=B1

2nd, if you need to test t see if a range contains a specified value or string, there are a few ways to do that...
=countif(B1:B5,A1)>0
or
=match(A1,B1:B5,0)
to get that to return T/F...
=iserror(match(A1,B1:B5,0))

 

[KuraiChikara]

Thank you but that didnt really help. I could use Countif because it reutrns 1 if true and 0 if false, but I was really looking to manipulate the message back like you can do in the IF function "True" "False" "Blue" "Banana" etc.

MATCH and ISERROR with MATCH don't work.

With the same setup:
In cell A1 I have "Sup 1"
In cells B1-B5 I have Sup 3, Sup 4, Sup 5, Sup 6 and Sup 1

I put in =MATCH(A1,B1:B5,0) in Cell C1 and it returns the number 5.

With ISERROR MATCH, I use =ISERROR(MATCH(A1,B1:B5,0)) in Cell C1 and it returns FALSE, when B5 contains the TRUE criteria in this example.

 

[StephenCrump]

Thank you but that didnt really help.

Did you try Ford's formula: =COUNTIF(B1:B5,A1)>0

It returns TRUE which I think is the answer you wanted?

Another alternative is: =NOT(ISERROR(MATCH(A1,B1:B5,)))

Or more succinctly: =ISNUMBER(MATCH(A1,B1:B5,))

 

[Murphy123]

Thank you but that didnt really help. I could use Countif because it reutrns 1 if true and 0 if false, but I was really looking to manipulate the message back like you can do in the IF function "True" "False" "Blue" "Banana" etc.

MATCH and ISERROR with MATCH don't work.

With the same setup:
In cell A1 I have "Sup 1"
In cells B1-B5 I have Sup 3, Sup 4, Sup 5, Sup 6 and Sup 1

I put in =MATCH(A1,B1:B5,0) in Cell C1 and it returns the number 5.

With ISERROR MATCH, I use =ISERROR(MATCH(A1,B1:B5,0)) in Cell C1 and it returns FALSE, when B5 contains the TRUE criteria in this example.

Hi
Could you not put the Countif inside an If and have whatever message you want

Regards
Murphy123

 

[FDibbins]

Thank you but that didnt really help. I could use Countif because it reutrns 1 if true and 0 if false, but I was really looking to manipulate the message back like you can do in the IF function "True" "False" "Blue" "Banana" etc.

Your initial question was that you wanted to return either TRUE or FALSE (a standard logical test), I shows ways do to that. Now you indicate that you that there are other options that you need to have returned.

Please give a more realist sample of your data, as well as what you expect from that?