Applying IFERROR in an array

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
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
IXIYIZJAJBJCJDJEJFJGJHJIJJJKJLJMJN
385PlayerTeamGmsRecYdsAvgYPGLgTDFDTarYACARI
386Larry FitzgeraldAri161071,0239.5663.9326591503580.71
387David JohnsonAri16808791154.9584421206610.67Larry Fitzgerald
388John BrownAri153951713.334.530t228731000.53David Johnson
389Jermaine GreshamAri163739110.624.434219611380.61John Brown
390J.J. NelsonAri153456816.737.980t626741740.46Jermaine Gresham
391Darren FellsAri1414154111137t1718900J.J. Nelson
392Andre EllingtonAri1612857.085.3160419720Darren Fells
393Jaron BrownAri7111871726.751t1622590Andre Ellington
394Brittan GoldenAri1288210.36.8221414120Jaron Brown
395Jeremy RossAri24379.2518.51514470Brittan Golden
396Ifeanyi MomahAri5250251027024160Jeremy Ross
397Troy NiklasAri31000000200Ifeanyi Momah
398Kerwynn WilliamsAri101660.6600270Troy Niklas
399Chris JohnsonAri40000000100Kerwynn Williams
400Hakeem VallesAri110000000200Chris Johnson
401Julio JonesAtl14831,4091710175t6641293960.64Hakeem Valles
402Mohamed SanuAtl155965311.143.559433812870.73#NUM!
403Devonta FreemanAtl16544628.5628.935222654160.83#NUM!
404Taylor GabrielAtl133557916.544.576t621502840.7#NUM!
405Tevin ColemanAtl133142113.632.449316403590#NUM!
406Jacob TammeAtl8222109.5526.21931331960#NUM!

<tbody>
</tbody>
NFL Player Stats


Array Formulas
CellFormula
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>
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

AkaTrouble

Well-known Member
Joined
Dec 17, 2014
Messages
1,544
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

New Member
Joined
Jul 4, 2016
Messages
48
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

Well-known Member
Joined
Dec 17, 2014
Messages
1,544
you can edit an array formula but you have to select the array it applies to and then press F2 key ( i think)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,335
Messages
5,547,332
Members
410,785
Latest member
phillippaige
Top