Stores by City and State - Changes in DATA are not reflected in RESULTS

[Yodelayheewho]

Hello,

Attached is a spreadsheet with two worksheets: DATA holds the, well, data. RESULTS is where I have my array formula. The formula appeared to be working properly. However, when I tested it, by changing the cities listed in the DATA sheet - all to the same city - the formulas would not recalculate and change the results.

I've never run into this before, so I'm puzzled.
Thank you in advance!

 

[herofox]

hi , you can use this array

=IFERROR(INDEX(DATA!$C$2:$C$5000,SMALL(IF(DATA!$I$2:$I$5000=$B$1,IF(DATA!$J$2:$J$5000=$B$2,ROW(A$2:A$5000)-ROW(A$2)+1)),ROWS($B$3:B3))),"")

 

[Yodelayheewho]

This is really helpful, but I did discover an anomaly that I simply can't figure out. I updated the spreadsheet and formulas to match my 'real' spreadsheet exactly. However, even though my MSTR_DATA only fills to Row 17, I have to enter Row 23 after SMALL(IF for Column I, but, I do not need to do this for Columns C or J.
=IFERROR(INDEX(MSTR_DATA!$C$2:$C$17,SMALL(IF(MSTR_DATA!$I$2:$I$23=$H$1,IF(MSTR_DATA!$J$2:$J$17=$H$2,ROW(G$2:G$23)-ROW(G$2)+1)),ROWS($H$3:H3))),"")

So, strange.

 

[herofox]

my dear every Result in Column H is correct,I don't Know what the problem with You?!!
every thing is ok ,it's good array (Ctrl+shift+Enter) ,not as Normal only Enter

Please do not delay in reply ​

 

[Yodelayheewho]

You are correct, what you sent back does work. Thank you.

However, I'm puzzled at why the ranges(see in red) have to be larger than the actual range, in order for the formula to work. In the sample spreadsheet, my source list only has 17 rows, but the formula doesn't work unless you set the range for >17 (ex: $C$2:$C$170 vs $C$2:$C$17).

What I sent you: =IFERROR(INDEX(MSTR_DATA!$C$2:$C$17,SMALL(IF(MSTR_DATA!$I$2:$I$23=$H$1,IF(MSTR_DATA!$J$2:$J$17=$H$2,ROW(G$2:G$23)-ROW(G$2)+1)),ROWS($H$3:H3))),"")

What you sent back:
=IFERROR(INDEX(MSTR_DATA!$C$2:$C$170,SMALL(IF(MSTR_DATA!$I$2:$I$170=$H$1,IF(MSTR_DATA!$J$2:$J$170=$H$2,ROW(G$2:G$170)-ROW(G$2)+1)),ROWS($H$3:H3))),"")

Anyways, I've got it to work, just mysterious on why the ranges reference has to be larger.

Thank you!

 

[herofox]

Certainly this is not a measure
But I wanted the range to be larger so maybe you could increase the data in the future

 

[Yodelayheewho]

Thank you herofox! Stay healthy!