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"?
eracerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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:

=K1=S16:S20

If you saw this all by itself in a cell... it would probably make more sense why it couldn't be evaluated.
0
mekaneck84Commented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.