Understanding how this [formula] works

[jmendenhall22]

I have the following codes in excell workbooks and want to modify what they do but cannot make them work. I thought I knew what each part did but this is not the case (I found out). I am hoping if someone can explain what each part does by breaking it down then not only will I learn but I should be able to modify!!!

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

I have changed them and used the ctrol shift enter keys but get no results.
Thank you for looking and or explaining.
John

[JBeaucaire]

You have to work from the inside out.

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

This formula creates an array that lists all the row numbers that hold "TRUE" for all the tests done inside. Then the SMALL function is used to take one of those rows and display a value from that row.

The first value is retrieved using the number 1 in the second parameter of the SMALL(array, k) function. That number one is generated in the first cell by this part of the formula near the end:
ROWS(CHARTASSIGN!$C$3:$C3)

ROWS(C3:C3) = 1 row which = 1

Each time you copy the formula down, eveything in the formula stays exactly the same execpt that part of the formula. The next row down that part will change to:
ROWS(CHARTASSIGN!$C$3:$C4)

ROWS(C3:C4) = 2 rows which = 2

The rest is pretty standard. The array in the SMALL(array, k) function is being built by all the IF(tests+tests+test, ROWS(Range)) in your formula, and I think you understand that. Put an AND method is what I typically see, not plus signs:
IF(AND(test, tests, test), ROWS(Range))

Now the SMALL array is built, the first cell is ready to pull out the first value in the array. That is result is fed into the INDEX(range, row) as the "row" answer, and that mathing value from the "range" is revealed.

So, your formula, I might write it as:

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


You can click on the Formula > Evaluate Formula function to watch the formula unfold slowly.

[Chance2]

John,

There are basically three parts to these formulas:
1) IFERROR(condition, result): The condition is INDEX(...) and the result is the final ,""). Simply said return a blank if the formula produces an error.
2) INDEX(array, row, column): Return the value from the array in row/column (in this case the column is the only one selected C on sheet CHARTASSIGN). The row is using the SMALL and ROW commands to determine which row to return.
3) SMALL(array, nth value): This returns the number of the nth smallest value in your array. i.e. - If your array was 1, 3, 5, 7 and your nth value was 2 it will return 3 (the second smallest number). The trick here is in the IF and ROW statements. The IF statements are each looking to see if a condition is met If the first 3 characters of the search range are not "DR." then 0, otherwise 100 + If the search range is not blank then 0, otherwise 100 and finally return the row number (-2 to reset to 1 as your start). The IF + IF + ROW is making up a list of numbers for the SMALL to search through (If Cell C3 was "DR. NO" then the value would be 100 + 0 + 1 = 101, If Cell C4 was blank then it would be 0 + 100 + 2 = 102, If cell C5 was "JAMES BOND" then it would be 0 + 0 + 3 = 3 and so on down to C30. The last ROWS statement is finding which row your formula is on to determine the nth value. So if this formula was in A1 it would look for the smallest value (in my example 3) and return the value in C3 as the result.
If this was copied down to A2 (and no more data below C5) would error out because the second smallest value is 101 which is outside the search range (C3:C30).
This is an array formula, so it has to be entered with CSE.

The second formula is doing basically the same thing on Sheet2 except it has an error in the final ROWS statement:
ROWS(sheet2!$D$D4$D4) should be ROWS(sheet2!$D4:$D4).