convert Array Formula to Non-Array

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
147
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
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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
Joined
Sep 8, 2018
Messages
147
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))),"")}
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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

Board Regular
Joined
Sep 8, 2018
Messages
147
Sheet "shift trades" ex

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

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

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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))),"")
 

Forum statistics

Threads
1,078,148
Messages
5,338,522
Members
399,240
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top