You are here:

# Excel/functions, array breakdown

Question
Hello Bill,
I have the following formulas in my workbook and want to expand on them or use them in different sheets but no matter how much I read and look online I can't seem to figure them out. What I am looking for is somebody who can break them down for me so I can see what each function does in the overall picture. The books all use only one function at a time and as I try to break them out and use them or alter them I get nothing but errors. The codes are as follows. Any help would be greatly appreciated.

=IFERROR(INDEX(CHARTASSIGN!\$C\$3:\$C\$31,MATCH(A17,CHARTASSIGN!\$B\$3:\$B\$31,0),0),"")

=IFERROR(INDEX(CHARTASSIGN!\$C\$3:\$C\$30,SMALL(IF(LEFT(CHARTASSIGN!\$C\$3:\$C\$30,3)<>"DR.",0,100)+IF(CHARTASSIGN!\$C\$3:\$C\$30>"",0,100)+ROW(CHARTASSIGN!\$S\$3:\$S\$30)-2,ROWS(CHARTASSIGN!\$C\$3:\$C3)),0),"")

=IFERROR(INDEX(sheet2!\$D\$4:\$D\$74,SMALL(IF(LEFT(sheet2!\$D\$4:\$D\$74,3)<>"7,7mtg",0,100)+IF(sheet2!\$D\$4:\$D\$74>"",0,100)+ROW(sheet2!\$S\$3:\$S\$30)-2,ROWS(sheet2!\$D\$D4\$D4)),0),"")

Thank you for looking and hope you can help on any. I really want to learn and not just have someone write things for me.
John

What I could write for you to break them down would be a lot to write.  But in a nutshell, this is what's happening.

If you use functions in their simplest form then all of the arguments would be hard typed.  For example, =IF(A1=1,10,500# means that if A1 contains a 1 then put 10 in that cell. If not then put 500.  But the TRUE part, 10, may have be more logical and thus have to be calculated.  So, another function may be inserted for the 10.  For example,

=IF#A1=1,IF(A2=1,10,100),500)

The inner IF statement is now more dynamic.  In the functions you referenced, the arguments they need are having to be calculated.  You'll have to see what those functions' arguments are first of all if they were hard typed.  Then, you'll see, hopefully, how those arguments are being calculated dynamically.

Hopefully this helps.

Bill