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?

 

[DJ'sGiGi]

What does it return and what is expected?

What should be happening with the array is this...I have a table on a spreadsheet called Data that has information from row 2 through 217. On another spreadsheet I have a blank table that should populate based on the choice made from a drop down list in cell M3.

Here's a general example, let's say the table on the Data spreadsheets contains information that is categorized by food type (e.g. meats, vegetables, fruits, starches, diary) in column C, on anther spreadsheet I have those same categories in a drop down list. If I choose meats in the drop down list, the blank table should fill in all the foods that have meats as the category (column C on the data spreadsheet) and put that info into column A on the current spreadsheet. The array should not leave any blank rows.

Again, the formula works fine for data in rows 2:170 (the old data) but it's ignoring the new rows of data that I've added (rows 171:217). As you can see below, the formula is exactly the same except the reference to row 217.

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))),"")}

I'm totally stumped.

 

[Aladin Akyurek]

Try please either to provide a copy of your file via an external source like dropbox or to literally respond to the diganostic questions like:

What do we get with:

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

=SUMPRODUCT((Data!$C$171:$C$217=$M$3)+0)

=SUMPRODUCT((TRIM(Data!$C$171:$C$217)=$M$3)+0)

 

[DJ'sGiGi]

I attempted to respond to the countif question, so maybe I should try sharing the file via dropbox. Can I share with you through this forum or take a different route?

 

[Aladin Akyurek]

I attempted to respond to the countif question, so maybe I should try sharing the file via dropbox. Can I share with you through this forum or take a different route?

A public dropbox is ok.

Try please either to provide a copy of your file via an external source like dropbox or to literally respond to the diganostic questions like:

What do we get with:

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

=SUMPRODUCT((Data!$C$171:$C$217=$M$3)+0)

=SUMPRODUCT((TRIM(Data!$C$171:$C$217)=$M$3)+0)

These should be easy to answer, no?

 

[DJ'sGiGi]

https://www.dropbox.com/s/jv88ouoihzyzrwt/Test.xlsx

 

[DJ'sGiGi]

A public dropbox is ok.



These should be easy to answer, no?

https://www.dropbox.com/s/jv88ouoihzyzrwt/Test.xlsx

 

[Aladin Akyurek]

https://www.dropbox.com/s/jv88ouoihzyzrwt/Test.xlsx

Aggressive Allocation (M3) does not occur in Data!$C$171:$C$217.

 

[DJ'sGiGi]

Aggressive Allocation (M3) does not occur in Data!$C$171:$C$217.

Correct, it occurs somewhere in rows 2:170 which work fine. The problem is I added additional data and the table now extends to row 217. If you use the category selector (A3) and choose Corporate Bond (which is on row 206) you will get nothing. None of the securities from row 171:217 will show up. That's my problem. The array works fine as long as data is in rows 2:170 but it totally ignores the data from 171:217.

 

[Aladin Akyurek]

Correct, it occurs somewhere in rows 2:170 which work fine. The problem is I added additional data and the table now extends to row 217. If you use the category selector (A3) and choose Corporate Bond (which is on row 206) you will get nothing. None of the securities from row 171:217 will show up. That's my problem. The array works fine as long as data is in rows 2:170 but it totally ignores the data from 171:217.

Nothing wrong with that either...

Not just Corporate Bond must occur in the C-range, also 1 in the X-range. The latter is not the case.

 

[DJ'sGiGi]

Nothing wrong with that either...

Not just Corporate Bond must occur in the C-range, also 1 in the X-range. The latter is not the case.

GEEZ LOUISE!!! I am so sorry. It never occurred to me to check my second condition. I simply needed to copy the formula down. I saw the fields weren't populating and instantly panicked.

Thank you so much.

You ROCK!!!