# convert Array Formula to Non-Array

#### hajiali

##### Board Regular
Hello All just trying to get the following array formula to a non-array

Code:
``{=IFERROR(INDEX(TRADES!\$D\$2:\$D\$2000,MATCH(1,(TRADES!\$A\$2:\$A\$2000=B3)*(TRADES!\$C\$2:\$C\$2000=DATE(\$F\$1,\$N\$1,\$E\$1)),0)),"")}``
Thanks

##### Active Member
Hi Haijali,

Try
Code:
``=IFERROR(INDEX(Trades!\$D\$2:\$D\$2000,AGGREGATE(15,6,(ROW(Trades!\$A\$2:\$A\$2000)-1)/((Trades!\$A\$2:\$A\$2000=B3)*(Trades!\$C\$2:\$C\$2000=DATE(\$F\$1,\$N\$1,\$E\$1))),1)),"")``

#### hajiali

##### Board Regular
Thank you so Much Toadstool much appreciate it.

can the below be done as well?

Code:
``{=IFERROR(INDEX('SHIFT TRADES'!\$E\$2:\$E\$2001,SMALL(IF('SHIFT TRADES'!\$C\$2:\$C\$2001=DATE(\$J\$1,\$G\$1,\$H\$1),ROW('SHIFT TRADES'!\$E\$2:\$E\$2001)-ROW('SHIFT TRADES'!\$E\$2)+1),ROWS('SHIFT TRADES'!\$E\$2:E2))),"")}``

##### Active Member
The SMALL function of AGGREGATE should be available and is not an array formula, but I'm struggling to see what you are trying to do.

#### hajiali

##### Board Regular

 DATE NAME 11/15/19 BOB 11/16/19 JIM 11/18/19 KEVIN 11/15/19 TOM 11/15/19 SMITH 11/17/19 BILL 11/15/19 TIM 11/18/19 WILL

<tbody>
</tbody>

In the sheet were this formula if the DATE(\$J\$1,\$G\$1,\$H\$1) = 11/15/19 then the results will be as follows: "BOB, TOM,SMITH,TIM"

if DATE(\$J\$1,\$G\$1,\$H\$1)=11/18/19 then results will be: "KEVIN, WILL" and so on. in other words pull all names in above table when the date to the right match whats in the cell. hope this clarifies.

##### Active Member
Ah! OK, this should work but if you've thousands of rows I'd replace the IFERROR (which is inefficient) with a check if the current row exceeds the COUNTIF of matches by dates.

Code:
``=IFERROR(INDEX('SHIFT TRADES'!\$E\$2:\$E\$2001,AGGREGATE(15,6,(ROW('SHIFT TRADES'!\$C\$2:\$C\$2001)-1)/('SHIFT TRADES'!\$C\$2:\$C\$2001=DATE(\$J\$1,\$G\$1,\$H\$1)),ROWS('SHIFT TRADES'!\$C\$2:\$C2))),"")``