Applying IFERROR in an array

[MrBurn5]

I figured out my problem in my post the other day - to a point. I can't seem to apply an IFERROR to the array in the JM column without running in to errors or completely removing the player names. Any thoughts?



Excel 2016 (Mac) 32 bit

	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI	JJ	JK	JL	JM	JN
385	Player	Team	Gms	Rec	Yds	Avg	YPG	Lg	TD	FD	Tar	YAC				ARI
386	Larry Fitzgerald	Ari	16	107	1,023	9.56	63.9	32	6	59	150	358	0.71
387	David Johnson	Ari	16	80	879	11	54.9	58	4	42	120	661	0.67			Larry Fitzgerald
388	John Brown	Ari	15	39	517	13.3	34.5	30t	2	28	73	100	0.53			David Johnson
389	Jermaine Gresham	Ari	16	37	391	10.6	24.4	34	2	19	61	138	0.61			John Brown
390	J.J. Nelson	Ari	15	34	568	16.7	37.9	80t	6	26	74	174	0.46			Jermaine Gresham
391	Darren Fells	Ari	14	14	154	11	11	37t	1	7	18	90	0			J.J. Nelson
392	Andre Ellington	Ari	16	12	85	7.08	5.3	16	0	4	19	72	0			Darren Fells
393	Jaron Brown	Ari	7	11	187	17	26.7	51t	1	6	22	59	0			Andre Ellington
394	Brittan Golden	Ari	12	8	82	10.3	6.8	22	1	4	14	12	0			Jaron Brown
395	Jeremy Ross	Ari	2	4	37	9.25	18.5	15	1	4	4	7	0			Brittan Golden
396	Ifeanyi Momah	Ari	5	2	50	25	10	27	0	2	4	16	0			Jeremy Ross
397	Troy Niklas	Ari	3	1	0	0	0	0	0	0	2	0	0			Ifeanyi Momah
398	Kerwynn Williams	Ari	10	1	6	6	0.6	6	0	0	2	7	0			Troy Niklas
399	Chris Johnson	Ari	4	0	0	0	0	0	0	0	1	0	0			Kerwynn Williams
400	Hakeem Valles	Ari	11	0	0	0	0	0	0	0	2	0	0			Chris Johnson
401	Julio Jones	Atl	14	83	1,409	17	101	75t	6	64	129	396	0.64			Hakeem Valles
402	Mohamed Sanu	Atl	15	59	653	11.1	43.5	59	4	33	81	287	0.73			#NUM!
403	Devonta Freeman	Atl	16	54	462	8.56	28.9	35	2	22	65	416	0.83			#NUM!
404	Taylor Gabriel	Atl	13	35	579	16.5	44.5	76t	6	21	50	284	0.7			#NUM!
405	Tevin Coleman	Atl	13	31	421	13.6	32.4	49	3	16	40	359	0			#NUM!
406	Jacob Tamme	Atl	8	22	210	9.55	26.2	19	3	13	31	96	0			#NUM!

<tbody>
</tbody>

NFL Player Stats​

Array Formulas Cell Formula JM387 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A1)))} JM388 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A2)))} JM389 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A3)))} JM390 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A4)))} JM391 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A5)))} JM392 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A6)))} JM393 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A7)))} JM394 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A8)))} JM395 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A9)))} JM396 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A10)))} JM397 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A11)))} JM398 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A12)))} JM399 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A13)))} JM400 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A14)))} JM401 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A15)))} JM402 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A16)))} JM403 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A17)))} JM404 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A18)))} JM405 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A19)))} JM406 {=INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A20)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[AkaTrouble]

i am not good on array formulas but the iferror function is a formula wrapper so you simply wrap an entire formula in if error

=IFERROR("my formula,"what to put if it is an error")

so my guess for your array formula would be

=IFERROR(INDEX($IX$386:$IX$886, SMALL(IF($JM$385=$IY$386:$IY$886, ROW($IY$386:$IY$886)-MIN(ROW($IY$386:$IY$886))+1,""),ROW(A20))),"my error message")

entered with CTRL + SHIFT + ENTER as an array formula

 

[MrBurn5]

Awe geez... Yea total brain fart on my part. I don't use arrays that often myself. Apparently you can't just edit an array formula, but I could create the new formula and apply it.

 

[AkaTrouble]

you can edit an array formula but you have to select the array it applies to and then press F2 key ( i think)