I have some data like:
DATE INFO -------- ---- 2000/1/1 A 2000/1/2 B 2000/1/2 C 2000/1/2 D 2000/1/3 E I want to retrieve all the INFO associated with each date. The result would be:
DATE RESULT -------- ------ 2000/1/1 A 2000/1/2 BCD 2000/1/3 E (I do not mind if the result is in columns)
11 Answer
If you have Office 365 Excel then you can use this array formula:
=CONCAT(IF($A$2:$A$6=D2,$B$2:$B$6,"")) Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
If you do not have Office 365 Excel and you are okay with the results in separate columns then use this:
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$6)/($A$2:$A$6=$D2),COLUMN(A1))),"") Put that in the first cell, copy over and down.
