convert Array Formula to Non-Array

[hajiali]

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

{=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

 

[Toadstool]

Hi Haijali,

Try

=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]

Thank you so Much Toadstool much appreciate it.

can the below be done as well?

{=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))),"")}

 

[Toadstool]

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.

Please suply your sample data and results.

 

[hajiali]

Sheet "shift trades" ex

[TABLE="width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]NAME
[/TD]
[/TR]
[TR]
[TD]11/15/19[/TD]
[TD]BOB
[/TD]
[/TR]
[TR]
[TD]11/16/19[/TD]
[TD]JIM[/TD]
[/TR]
[TR]
[TD]11/18/19[/TD]
[TD]KEVIN[/TD]
[/TR]
[TR]
[TD]11/15/19[/TD]
[TD]TOM[/TD]
[/TR]
[TR]
[TD]11/15/19[/TD]
[TD]SMITH[/TD]
[/TR]
[TR]
[TD]11/17/19[/TD]
[TD]BILL[/TD]
[/TR]
[TR]
[TD]11/15/19[/TD]
[TD]TIM[/TD]
[/TR]
[TR]
[TD]11/18/19[/TD]
[TD]WILL[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Toadstool]

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.

=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))),"")