Array Ignoring last cell reference

[DJ'sGiGi]

I have the following array:

{=IFERROR(INDEX(Data!$A$2:$A$170,SMALL(IF(Data!$C$2:$C$170=$M$3,IF(Data!$X$3:$X$170=1,ROW(Data!$A$2:$A$170)-ROW(Data!$A$2)+1)),ROWS($A$7:A7))),"")}

which works perfectly (for the most part.) The only problem I have is that the array will not return the data that is on row 170 (the last row of data) and I'm not sure how to fix it. The array basically says look at the category in cell $M$3 and if cell X_ = 1, then return the data. Again, the data returns fine except when it should be returning the data in row 170. Any suggestions?

 

[jamtay317]

is the data in A170 the same as any of the other cells, if so you may have to drag it down more.

 

[Aladin Akyurek]

Try the following edit...

$X$3 --> $X$2

in:

{=IFERROR(INDEX(Data!$A$2:$A$170,SMALL(IF(Data!$C$2:$C$170=$M$3,
     IF(Data!$X$2:$X$170=1,ROW(Data!$A$2:$A$170)-ROW(Data!$A$2)+1)),
     ROWS($A$7:A7))),"")}

 

[jamtay317]

or you could try, iferror(index(Data!$A$2"$A$170,Aggregate(15,6.row(Data!$A$2:$A$170)-row(DATA!$A$2)+1/(Data!$C$2:$C$170=$M$3),ROWS($A$7:A7)),"")

please be advised this isnt an array formula that requires Ctrl+Shift+Enter

 

[DJ'sGiGi]

Try the following edit...

$X$3 --> $X$2

in:

{=IFERROR(INDEX(Data!$A$2:$A$170,SMALL(IF(Data!$C$2:$C$170=$M$3,
     IF(Data!$X$2:$X$170=1,ROW(Data!$A$2:$A$170)-ROW(Data!$A$2)+1)),
     ROWS($A$7:A7))),"")}

Woo-whoo!!! Works perfectly. Thanks Aladin.

 

[Aladin Akyurek]

Woo-whoo!!! Works perfectly. Thanks Aladin.

You are welcome.

 

[DJ'sGiGi]

Aladin, you helped me with an array before. Now I have a new issue. I'm using the same array, but I expanded the data in my table from row 2:170 to row 2:217. My array reference automatically updated fine but now the new array totally ignores any data in rows 171:217 (the new data). Any ideas why? The old and new formulae are below:

OLD: {=IFERROR(INDEX(Data!$A$2:$A$170,SMALL(IF(Data!$C$2:$C$170=$M$3,IF(Data!$X$2:$X$170=1,ROW(Data!$A$2:$A$170)-ROW(Data!$A$2)+1)),ROWS($A$7:A7))),"")}

NEW: {=IFERROR(INDEX(Data!$A$2:$A$217,SMALL(IF(Data!$C$2:$C$217=$M$3,IF(Data!$X$2:$X$217=1,ROW(Data!$A$2:$A$217)-ROW(Data!$A$2)+1)),ROWS($A$7:A7))),"")}

Thanks.

 

[Aladin Akyurek]

Aladin, you helped me with an array before. Now I have a new issue. I'm using the same array, but I expanded the data in my table from row 2:170 to row 2:217. My array reference automatically updated fine but now the new array totally ignores any data in rows 171:217 (the new data). Any ideas why? The old and new formulae are below:

OLD: {=IFERROR(INDEX(Data!$A$2:$A$170,SMALL(IF(Data!$C$2:$C$170=$M$3,IF(Data!$X$2:$X$170=1,ROW(Data!$A$2:$A$170)-ROW(Data!$A$2)+1)),ROWS($A$7:A7))),"")}

NEW: {=IFERROR(INDEX(Data!$A$2:$A$217,SMALL(IF(Data!$C$2:$C$217=$M$3,IF(Data!$X$2:$X$217=1,ROW(Data!$A$2:$A$217)-ROW(Data!$A$2)+1)),ROWS($A$7:A7))),"")}

Thanks.

What does this deliver?

=COUNTIF(Data!$C$171:$C$217,$M$3)

 

[DJ'sGiGi]

What does this deliver?

=COUNTIF(Data!$C$171:$C$217,$M$3)

If I use my drop down selector in M3 and select one of the choices its ignoring, it returns the actual number of items in those rows.

 

[Aladin Akyurek]

If I use my drop down selector in M3 and select one of the choices its ignoring, it returns the actual number of items in those rows.

What does it return and what is expected?