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

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
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:
ABC
4Color:Blue
First Name
Last Name
8
9
10
11#NUM!#NUM!
12#NUM!#NUM!
13#NUM!#NUM!
31Color
First Name
Last Name
32BlueRonaldWashington
33GreenMaryRamirez
34BluePeteRogers
35BlueSusieMcNickel

<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:
Code:
{=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:
Code:
=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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

abmati

Board Regular
Joined
Jul 9, 2010
Messages
175
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

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Color:</td><td style=";">Blue</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">First Name</td><td style=";">Last Name</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Ronald</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Pete</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Susie</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A8</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$32:$C$106,SMALL(<font color="Green">IF(<font color="Purple">$A$32:$A$106=$B$4,ROW(<font color="Teal">$A$32:$A$106</font>)-31</font>),ROWS(<font color="Purple">A$8:A8</font>)</font>),2</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Hope that helps.
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
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

Active Member
Joined
Dec 5, 2012
Messages
258
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

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,207
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top