Solved

# Excel amateur here (IF function returns #VALUE!)

Posted on 2014-08-04
246 Views
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"?
0
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
0

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.
0

LVL 19

Expert Comment

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

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)
0

## Featured Post

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.