Evaluating Arrays

This is my first post so bare with me as this this may be a bit long.

I have been working hard to improve my understanding of Excel and have been working through John Walkenbach's book Excel 2007 formulas and have gotten a bit stuck in my understanding of how to think about an array and particularly the one below. (sample attached)

{=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))}

This one is from pg 423 of the book designed to return a list of unique items in a range. My problem is when I use "Evaluate Formula" it is not apparent to me how it arrives at each of the separate determinations for each of the cells in the second column. The next to last evaluation step is seemingly the same for each cell that the evaluation is run on. But the result is different. I don't understand how in the whole of the evaluation stepping process, all steps are identical yet a different result is arrived in each cell. I am sure it is a stepping process but I can't see that in the Evaluate Formula window. If it doesn't show there then how is the array arriving at the correct result.

Thanks for anyone who might take a bit of time to look at this and help me to understand what is going on with the array.

Thanks

Because you confirmed the array formula (with CTRL+SHIFT+ENTER) after selecting the whole range, then the evaluation tool doesn't work 100% to show you what is going on... but basically the SMALL function is key to determining which string to return. If you evaluate the function up to the evaluation of this part: SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""), then you should see an array like: {1;"";"";"";5;""';"";"";9;11;"";"";14;"";"";17;"";""} the numerals identify where there is a unique string found... if you evaluate further you will see that the k factor for the SMALL function ROW(INDIRECT("1:"&ROWS(data))) evaluates to 1... this is because you selected the whole range and CSE confirmed it, but in reality, it evaluates based on the row number... So to get Cat in second cell it is really looking at 2nd smallest (which is position 5), etc...

If you google Array formulas you should get some sites that explain them a bit.

As an aside, I usually like to use this non-array formula to get the same result:

=INDEX(data,MATCH(0,INDEX(COUNTIF($B$4:$B4,data),0),0))

this one is entered in C5 only, then copied down. When you evaluate this one, you will see differences in each cell's evaluation. This one is counting number of matches in the data to what you have pulled above. It looks for the first 0 which means the first time it doesn't find what is already found above the position you are currently in.

And a trick to hide the errors you get when you run out is to wrap the formula in an error handler... for example:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(data,MATCH(0,INDEX(COUNTIF($C$4:$C4,data),0),0)))

Hope that helps a bit... I am not the best at explaining these things