Unique list using Index, Match, and CountIf

This was a post on a previous forum entry from yesterday. I'm new to this deal, and the post said that it was solved, so I'm not sure if anyone would get notifications to respond to it or not. Anyways, I'm trying to create a list of unique values from a much larger list. See the original response below to understand what is going on and what I am asking.

I copied and modified the formula listed in this quote, and it worked. However, I do not fully understand why, and I'd like to. Can someone walk me through the COUNTIF criteria that uses a range of cells; I've been playing on Excel with some stuff and can't seem to figure out what it is doing (for example, COUNTIF(A2:A1000, B2:B4)). I initially thought that it would count how many values in A2:A:1000 matched any of the values in B2:B4, but this doesn't seem to be the case. Can someone provide insight as to what it is doing?

Then, I would like someone to explain what this counted number will do in the MATCH function. It is in the lookup_array section, but I don't understand what good a lookup array of 0 or 6 or any single numerical value would do here. Thanks for any help provided.

Originally Posted by Tony Valko

Try this...

Assuming no empty cells within the range of interest in column A.

Data Range

	A	B	C
1	All Names	Uniques	Count
2	BOB	BOB	2
3	STACY	STACY	2
4	TOM	TOM	1
5	STACY	GEORGE	1
6	BOB
7	GEORGE

This array formula** entered in B2:

=IFERROR(INDEX(A$2:A$100,MATCH(0,COUNTIF(B$1:B1,A$2:A$100),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in C2:

=IF(B2="","",COUNTIF(A:A,B2))

Select B2:C2 and copy down until you get blanks.