Index/Match Multiple Criteria Return Based on Date

[john62290]

I have the below table that I would like to return Steve's 1st sale price, 2nd sale price, etc in a seperate table.

Date	Sales Rep	Price	Quantity
10/13/2022	Steve	330	150
11/3/2022	Bob	300	150
12/1/2022	Bob	350	200
12/5/2022	Steve	400	50
12/7/2022	Steve	330	150
1/19/2023	Steve	350	150
3/3/2023	Bob	375	300
3/30/2023	Steve	200	100

	1​	2​	3​	5​
Steve
Bob

1 being the first date for Steve and Bob. I am wanting a formula to return 330 for Steve and 300 for Bob in cells below 1 then give the next prices in cells below the 2.

THanks!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Sales Rep	Price	Quantity
2	13/10/2022	Steve	330	150		Bob	300	350	375
3	03/11/2022	Bob	300	150		Steve	330	400	330	350	200
4	01/12/2022	Bob	350	200
5	05/12/2022	Steve	400	50
6	07/12/2022	Steve	330	150
7	19/01/2023	Steve	350	150
8	03/03/2023	Bob	375	300
9	30/03/2023	Steve	200	100
10
Main

Cell Formulas
Range	Formula
G2:I2,G3:K3	G2	=TOROW(FILTER($C$2:$C$100,$B$2:$B$100=F2))
Dynamic array formulas.

 

[john62290]

Is there potentially a way for this filter to not be an array? Thanks

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Sales Rep	Price	Quantity
2	13/10/2022	Steve	330	150		Bob	300	350	375
3	03/11/2022	Bob	300	150		Steve	330	400	330	350	200
4	01/12/2022	Bob	350	200
5	05/12/2022	Steve	400	50
6	07/12/2022	Steve	330	150
7	19/01/2023	Steve	350	150
8	03/03/2023	Bob	375	300
9	30/03/2023	Steve	200	100
10
Main

Cell Formulas
Range	Formula
G2:I2,G3:K3	G2	=TOROW(FILTER($C$2:$C$100,$B$2:$B$100=F2))
Dynamic array formulas.

 

[Fluff]

In what way?

 

[john62290]

In what way?

I need to do some analysis on the results I return but the arrayed formula makes it tricky to use those cells.

 

[Fluff]

Why would a spilled formula be any harder to work with than a formula that needs to be dragged across?

 

[john62290]

Why would a spilled formula be any harder to work with than a formula that needs to be dragged across?

Resolved my problem. Sorry for any confusion. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.