Excel amateur here (IF function returns #VALUE!)

Posted on 2014-08-04
Last Modified: 2014-08-04
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"?
Question by:eracer
    LVL 19

    Accepted Solution

    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

    Author Closing Comment

    Thanks!  I don't fully understand the formula you supplied, but it works!
    I will do some research in order to understand this more.
    LVL 19

    Expert Comment

    by:Ken Butters
    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.

    Expert Comment

    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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Microsoft Office 2010 is Microsoft's latest edition to their office productivity collection. It "promises to include rich and powerful new ways to deliver your best work". So to start off, I have to say that I'm slightly disappointed... That beiā€¦
    Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
    This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
    The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now