• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Excel amateur here (IF function returns #VALUE!)

I'm running the following formula:

=IF(K1=S16:S20,T16, IF(K1=S21:S23,T21,T24))

K1 is a drop down pointing to a list at S16:S24

S16 = Salesperson 1
S17 = Salesperson 2
S18 = Salesperson 3
S19 = Salesperson 4
S20 = Salesperson 5
S21 = Salesperson 6
S22 = Salesperson 7
S23 = Salesperson 8
S24 = Salesperson 9

T16 is a value (company address)
T21 is a value (another company address)
T24 is a value (a third company address)

I've also tried this version of the formula, which also returns #VALUE!

=IF(K1=S16:S20,T16, IF(K1=S21:S23,T21, IF(K1=S24,T24)))

Logically, the formula makes sense to me.  Does it have something to do with the fact that the value of "K1" comes from the drop down list located at "S16:S24"?
  • 2
1 Solution
Ken ButtersCommented:
With the format you have, you are asking whether or not the value in a single cell... K1,... is equal to a value in a range of cells.    That doesn't make sense to Excel.  What you really want to know is if the value in K1 exists in a range of cells.

I think the following formula would get you what you are after.
=IF(COUNTIF(S16:S20,K1)>0,T16, IF(COUNTIF(S21:S23,K1)>0,T21,T24))

Count the number of times that the value in K1 occurs in the range S16 to S20.  If that count is greater than zero... (meaning it is present)... then use address from T16

Count the number of times that the value in K1 occurs in the range S21 to S23.  If that count is greater than zero... (meaning it is present)... then use address from T21

Else use the address in T24
eracerAuthor Commented:
Thanks!  I don't fully understand the formula you supplied, but it works!
I will do some research in order to understand this more.
Ken ButtersCommented:
Glad I could help...

When troubleshooting this stuff... sometimes it is easier to break it down into pieces.
for example.... on your original formula in the test part of the IF..... excel would have to evaluate this:


If you saw this all by itself in a cell... it would probably make more sense why it couldn't be evaluated.
When dealing with a range of values,  you need to enter you formula as an array formula. To do this, instead of pressing <enter> when finished typing, press <ctrl><shift><enter>. The formula will then get brackets { } around it to signify that it is an array formula.

This will eliminate the #VALUE! error, but your formula still won't return what you want it to, because the IF function will look only at the first cell in the array. You'll need to add the "OR" function, so that, if K1 matches anything in the S16:S20 range, that it returns a single TRUE value, and therfore satisfies the "IF" function.

=IF(OR(K1=S16:S20),T16, IF(OR(K1=S21:S23),T21,T24))
(complete by ctrl-shift-enter)

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now