How to shorten formula as too many arguments

[sprewett123]

Hi,

I am trying to reduce the length of my formula by including the "AND" function but cannot get it to work...can anyone help...

{=IFERROR(INDEX(Download!E:E,SMALL(IF(Download!$J:$J="Dan Cooper",IF(Download!$L:$L="open",IF(Download!$M:$M>=70,IF(Download!$R:$R=A1,ROW(Download!$J:$J))))),ROWS(B$6:B6))),"")}

Thanks!

[TheCman81]

Because you have multiple criterias you won't shorten it by much, to include the AND function and remove most of the IF's you can use this

=IFERROR(INDEX(Download!E:E,SMALL(IF(AND(Download!$J:$J="Dan Cooper",Download!$L:$L="open",Download!$M:$M>=70,Download!$R:$R=A1),ROW(Download!$J:$J)),ROWS(B$6:B6))),"")

[Tony Valko]

Because you have multiple criterias you won't shorten it by much, to include the AND function and remove most of the IF's you can use this

=IFERROR(INDEX(Download!E:E,SMALL(IF(AND(Download!$J:$J="Dan Cooper",Download!$L:$L="open",Download!$M:$M>=70,Download!$R:$R=A1),ROW(Download!$J:$J)),ROWS(B$6:B6))),"")

That won't work.

The AND function returns a single result where you need an array.

[Tony Valko]

Not sure what you're wanting to do.

As is, and for what the formula is doing, there's not much to shorten.

One thing that can help make the formula more efficient is to not use the entire columns as range references. Use smaller specific ranges. Array formulas evaluate every cell that they reference. So, your formula is evaluating over 5 million cells and that takes a bit of time!

[AlKey]

Couldn't really test this formula so, just give it a try. I've set ranges from row 6 to row 200 so you can adjust them.

=IFERROR(INDEX(Download!E:E,SMALL(IF((Download!$J$6:$J$200="Dan Cooper")*(Download!$L$6:$L$200="open")*(Download!$M$6:$M$200>=70)*(Download!$R$6:$R$200=A1),ROW(Download!$J$6:$J$200)),ROWS(B$6:B6))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Tony Valko]

=IFERROR(INDEX(Download!E:E,SMALL(IF((Download!$J$6:$J$200="Dan Cooper")*(Download!$L$6:$L$200="open")*(Download!$M$6:$M$200>=70)*(Download!$R$6:$R$200=A1),ROW(Download!$J$2:$J$200)),ROWS(B$6:B6))),"")

The ROW function uses a different sized range.

So, by replacing the IF functions with array multiplication you shortened the formula by 4 keystrokes!

[AlKey]

The ROW function uses a different sized range.

So, by replacing the IF functions with array multiplication you shortened the formula by 4 keystrokes!

Well, don't forget I set static ranges which added few keystrokes. I think it can be reduced from ROWS(B$6:B6) to ROW(1:1) = 3 less)

Thanks Tony, this has been corrected "ROW(Download!$J$2:$J$200)"

[Tony Valko]

I think it can be reduced from ROWS(B$6:B6) to ROW(1:1) = 3 less

Yeah, but then that leaves the formula vulnerable to new row insertions.

[AlKey]

Yeah, but then that leaves the formula vulnerable to new row insertions.

That was only to reduce character count