Excel IF formula returning #VALUE! 2

[dhamDIT]

I am using the following If formula to search a list and return a specified number based on finding a match. In the formula window it seems to work, it shows me the returned value as 8 which it should be however in the actual cell I get a #VALUE!

=IF(N3=K83:K90,8)

Any help is apprciated

 

[xlhelp]

Please look at thread68-1297185

BTW, did you try to search for this in the forum?


Member- AAAA Association Against Acronym Abusers

 

[dhamDIT]

Hi I did look at this however my Transistion Formula Evaluation box is not checked
Thanks

 

[AnotherHiggins]

The logic of your formula doesn't work. You can't have a single cell (N3) equal to a range of cells (K83:K90).

Perhaps you want the CountIf function?

What are you trying to accomplish? We can probably help you write a proper formula.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Oh! Are you trying to use an array formula?

If so, you must enter it using [ctrl]+[Shift]+[Enter].

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dhamDIT]

Hi, what I am trying to do is this, If the value of N3 is found in the range of K83 to K90, I want to return a value of 8. The data is K83 to K90 is not in alphanumeric order and cannot be put in alphanumberic order.

Thanks

 

[AnotherHiggins]

try this:

=if(CountIf(K83:K90,N3)>0,8,[red]""[/red])

You don't say what you want to return if the value is NOT returned. The "" returns a 'zero-length-string' which will basically just look like a blank cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dhamDIT]

Thank you Anotherhiggens, this worked fine for me!

 

[xlhelp]

dhamDIT,

If this really helped you, may I point out a little link that's on the answer that John gave? It starts "Thank anotherhiggins for this...."

Member- AAAA Association Against Acronym Abusers

 

[dhamDIT]

Now am I trying to nest more than one Countif, but I think I am getting messed up with the brackets. If the N3 if is found in J3 to J13 I want it to deliver 8 as a value, if the N3 is found in J14:J20 I want it to deliver 6 else I would like it to say "open"

=IF(COUNTIF($J$3:$J$13,Sheet2!N3)>0,8,COUNTIF($J$14:$J$20,Sheet2!N3)>0,6,”OPEN”)

Thanks in advance for your help

 

[xlhelp]

If I am not mistaken I think you need
=IF(COUNTIF($J$3:$J$13,Sheet2!N3)>0,8,if(COUNTIF($J$14:$J$20,Sheet2!N3)>0,6,”OPEN”))


Member- AAAA Association Against Acronym Abusers

 

[dhamDIT]

Perfect, Thanks Xlhelp, I sent you the official thank you as well