Array formula to list all People with certain eye color returning blank cells.

[Tyron]

Hey everyone,

It seems to me that I am getting strange output of an excel formula and for the life of me I can't figure out why. Could someone explain why I am getting these results?

The goal of this array formula is to list all the first names of the people with a certain eye color. In this example Blue

only one worksheet is being used for the use of this question:

	A	B	C
4	Color:	Blue
	First Name	Last Name
8
9
10
11	#NUM!	#NUM!
12	#NUM!	#NUM!
13	#NUM!	#NUM!
31	Color	First Name	Last Name
32	Blue	Ronald	Washington
33	Green	Mary	Ramirez
34	Blue	Pete	Rogers
35	Blue	Susie	McNickel

<tbody>
</tbody>

The value in B4 is a data validation list box that has the different eye colors in it. They are Blue, Brown, Green

The formula that I am using in A8 through A10 is as follows:

{=INDEX($A$32:$C$106,SMALL(IF($A$32:$A$106=$B$4,ROW($A$32:$A$106)),ROW(A8:A8)),2)}

I ensured using the CTRL+SHIFT+ENTER keys in order to make this an Array formula and the formula changes to show ROW(2:2) and ROW(3:3) at the ends of the formula in A9 and A10.
For some reason as you can see above it has a blank cell for the values.

I did try one other formula that I found on the web:

=INDEX($B$32:$B$106, SMALL(IF(ISNUMBER(SEARCH($B$4, $A$32:$A$106)), MATCH(ROW($A$32:$A$106), ROW($A$32:$A$106))), ROW(A8)))

But I just end up with an error. When I say to trace the error I get a pop-up that says: "The Error Tracer encountered existing arrows or a circular reference. Delete all tracer arrows or resolve the circular reference, and then choose Trace Error again." Only I can't find any arrows like you normally do. I guess I am not understanding why the formula is referring to the different ranges.

All help is appreciated as I have been struggling with this for the last week.

later

Ty

 

[abmati]

There is no need to apply an array formula if you use one more column as under:
Please add the following formula in Column "D" (Copy D32 to D33:D106)
D32=IF(A32=$C$2,ROW(A1),"X")
Then use the following formula A8 and B8 and copy as required:
A8=IFERROR(INDEX($A$32:$C$106,MATCH(SMALL($D$32:$D$106,ROW(A1)),$D$32:$D$106,0),2),"")
B8=IFERROR(INDEX($A$32:$C$106,MATCH(SMALL($D$32:$D$106,ROW(B1)),$D$32:$D$106,0),3),"")

Regards.

A.B. Mati
Doha Qatar

 

[Snakehips]

Tyron,

Two things....

ROW(A8:A8) will always resolve to 1
ROW($A$32:$A$106) will generate values 32 to 106, actual row numbers, whereas you want row relative to the range A32:C106 therefore need subtract 31

Excel 2007
	A	B
4	Color:	Blue
5	First Name	Last Name
6
7
8	Ronald
9	Pete
10	Susie
11
12
Sheet3

Cell Formulas
Range	Formula
A8	{=IFERROR(INDEX($A$32:$C$106,SMALL(IF($A$32:$A$106=$B$4,ROW($A$32:$A$106)-31),ROWS(A$8:A8)),2),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[Tyron]

Hey Abmati,

Thanks for your suggestion. Although I am confused to one thing. In D32 you indicated to copy a formula that indicates IF(A32=$C$2,ROW(A1),"X"). What is the value of C2 refering to?

Thanks in advance

Ty

 

[Tyron]

Hey Tony,

Thanks. This works like a charm. Hopefully I can find a way to better understand this formula as it seems a bit elusive as to why I needed to write it that way.

I think it is how the SMALL function is used with the rest. I will look again in a bit after I give my brain some food. lol. Hopefully then I will be able to understand it better.

later

Ty

 

[Snakehips]

You are welcome.

With one of the formula cells selected, click the Formula tab and then click Evaluate Formula.

Each subsequent click on Evaluate will show how Excel is evaluating the underlined element of the formula.

You may need to use the scroll bars to see all of it but it should aid your understanding.

You could change the 106 top of the range to say 35 just in the one formula whilst you evaluate it as that will make the evaluation somewhat clearer.