The Scenario:
So, today at work my computer ran an MS Office update. Then, about 30 minutes later someone came up to me with what seemed like a simple request: Instead of using a VLOOKUP to return just the first match, they wanted a delimited list of all matches. (e.g. "which offices has this user visited this week"?) Seemed simple enough.
So, working with some junk data (see below), I swiftly wrote the following quick demonstration line:
=TEXTJOIN(", ", TRUE, IFERROR(INDEX(B$1:B$11, AGGREGATE(15, 6, ROW(A$1:A$11)/--(A$1:A$11=E1), ROW(A$1:A$11))),""))
Hit Enter, and... 6. Not quite what I was expecting (6, 35, 19, 56, 47), so I say "sorry about that", go back into the cell, and hit Ctrl+Shift+Enter:
6, again.
So, ran the "Evaluate Formula", and it turns out that that ROW(A$1:A$11) in the k position (last argument) of AGGREGATE was returning 1 instead of {1,2,3,4,5,6,7,8,9,10,11}, regardless of whether or not you were using a normal or Array formula.
So, I type the array out manually:
=TEXTJOIN(", ", TRUE, IFERROR(INDEX(B$1:B$11, AGGREGATE(15, 6, ROW(A$1:A$11)/--(A$1:A$11=E1), {1,2,3,4,5,6,7,8,9,10,11})),""))
End result: 6. I Evaluate Formula again, and AGGREGATE is discarding everything except the first value from my array. If I drop the INDEX, and just run TEXTJOIN on the AGGREGATE results, it works fine. Similarly, =SUM(ROW(B1:B11)) works fine, but =SUM(INDEX(B1:B11, ROW(B1:B11), 1)) does not.
The Question:
Now, I am 95% sure that I used to be able to get this INDEX(.., AGGREGATE(..)) and SUM(INDEX(..)) to work properly, instead of only returning the first value - is this something that haschanged about how certain chains of functions work with Arrays, or am I just misremembering?
(For those interested, in the end we went with the Array Formula =TEXTJOIN(", ", TRUE, IF(A$1:A$11=E1, B$1:B$11, "")) instead)
Junk data used in testing:
A | 6
A | 35
A | 19
B | 33
B | 46
B | 72
A | 56
C | 41
C | 20
B | 52
A | 47
