Array not working properly

Hello, Gurus!

Someone (read: “me”) decided it’d be a good idea for me to try using arrays in a formula without proper experience and/or supervision, and the result is my needing help before I or someone gets hurt. Obviously, I’m not doing something correctly, but Excel accepts the formula, so I’m hoping the fix is easy. The formula below (and in attached workbook’s Column D) is only returning “true” result for the first value in each array; anything else is giving me the “false” result; where did I go wrong?

Please Login or Register  to view this content.

Try changing
A2={1,2,3}
to
OR(A2={1,2,3})

Do that for each occurrence using {a constant array}

I love your humor.

Change A2={1,2,3} to OR(A2={1,2,3})

Do this for all of them.

FYI, here's how I would normally do this:

=IFERROR(LOOKUP(A2,{1,4,7,12},{"1 to 3 Days Late","4 to 6 Days Late","7 to 11 Days Late","+11 Days Late"}),"Early or On Time")

Originally Posted by 63falcondude

I love your humor.

There's a lot of clinical chat around here; tried to lighten it up a bit!

Thank you both; your solutions worked like a charm! Rep coming your way!

Originally Posted by 63falcondude

FYI, here's how I would normally do this:

=IFERROR(LOOKUP(A2,{1,4,7,12},{"1 to 3 Days Late","4 to 6 Days Late","7 to 11 Days Late","+11 Days Late"}),"Early or On Time")

Ok, that looks a LOT tidier; what exactly is that formula doing??

Glad we could help. I'll try my best to explain how the LOOKUP function works.

The function compares the number in A2 to the array {1,4,7,12}. It then finds the largest number in that array that is ≤ A2.

It takes the position of that number in the array at returns the string from the second array that is in the same position.

For example, if A2=6, the largest number in the first array that is ≤ 6 is 4. 4 is in the second position of the array {1,4,7,12} so the formula would return "4 to 6 Days Late", which is in the second position of the second array {"1 to 3 Days Late","4 to 6 Days Late","7 to 11 Days Late","+11 Days Late"}

The IFERROR is to take values of A2 < 1 (the first value of our first array) into account. You could change the formula to something like this:
=LOOKUP(A2,{-100,1,4,7,12},{"Early or On Time","1 to 3 Days Late","4 to 6 Days Late","7 to 11 Days Late","+11 Days Late"})
as long as no value in column A can be smaller than that first value.

That is AWESOME; makes all the sense in the world. I already know a half dozen places where I've needed that exact functionality, but did it with the clunky use of =IF(AND(A2>0,A2<4),IF... You can imagine how tedious that gets when you've multiple ranges. Thanks again, so much!

That (post #7) is the technical explanation. Here is how I think of it.

If A2 = [1,4) then return "1 to 3 Days Late"
If A2 = [4,7) then return "4 to 6 Days Late"
If A2 = [7,12) then return "7 to 11 Days Late"
If A2 = [12,∞) then return "+11 Days Late"

The brackets represent inclusive and the parenthesis represent non-inclusive.

The purple values make up the first array and the blue values make up the second array.

As you can see, numbers < 1 are not included. This would return an error (and is why I used IFERROR for values ≤ 1).

You're very welcome. Glad we could help. Thank you for the rep!

Perfect! Bookmarking this thread; I know this is going to come up again, and don't want to forget what you've taught me!