Index/Match question

[mikehk]

I would like to separate data from a long list using index/match function
but don't know the formula, please see the attached file.

Thank you.

[AliGW]

Have a look here: https://www.excelforum.com/excel-gen...-criteria.html

[José Augusto]

Hi

Try this array formula

Select H2:K2 and use the formula

=IFERROR(INDEX($A$2:$F$26,AGGREGATE(15,6,ROW($A$2:$A$26)/($B$2:$B$26=H$1)-1,ROWS($A$1:A1)),{1,4,5,6}),"")

Note: Confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
See the file

[mikehk]

I have copy pasted your formula and made slight changes to range

=IFERROR(INDEX($A$2:$F$10000,AGGREGATE(15,6,ROW($A$2:$A$10000)/($B$2:$B$10000=$L$1)-1,ROWS($A$1:A1)),{1,4,5,6}),"")

I am getting date from column 4,5,6

Please see the attached screenshot.

[José Augusto]

Hi
Where are your real data?

[mikehk]

Hi
Where are your real data?

Real data is in
Column A (has date)
Column B/C (has text)
Column D/E/F (has numbers)
Column G (has text)
Column H (has text)
Column I (has numbers)
Column J (has text)

[Czeslaw]

I suggest this option
formula "Date"

=IFERROR(AGGREGATE(15;6;$A$2:$A$260/($B$2:$B$260=$H$1);ROWS($2:2));"")

formula "High Low Low1"

=IFERROR(INDEX(D$2:D$260;AGGREGATE(15;6;ROW($1:$300)/($A$2:$A$260=$H2)/($B$2:$B$260=$H$1);COUNTIF($H$2:$H2;$H2)));"")

[mikehk]

I suggest this option
formula "Date"

=IFERROR(AGGREGATE(15;6;$A$2:$A$260/($B$2:$B$260=$H$1);ROWS($2:2));"")

formula "High Low Low1"

=IFERROR(INDEX(D$2:D$260;AGGREGATE(15;6;ROW($1:$300)/($A$2:$A$260=$H2)/($B$2:$B$260=$H$1);COUNTIF($H$2:$H2;$H2)));"")

Your formula works...thank you so much