INDEX/MATCH Query - Amendment needed

[Kirstym1918]

Hi all,

I have this formula below which INDEXs a list for every date of the year vertically G4:G369, I then created a match for the column next to the date so if the cell has a H, it returns the date from the INDEX range...BUT it keeps returning the same duplicated date over and over when I drag the formula down in to the next cell 38 cells I am using. I know this is what INDEX/MATCH will do but how to I get the formula to move on to the next match if the cell above has already retrieved it?

=INDEX($G$4:$G$369,MATCH($J$1,$H$4:$H$369,0))

Many Thanks

 

[Fluff]

It works for me

Excel 2013/2016
	G	H	I	J
3		H
4	01/01/2019	W		02/01/2019
5	02/01/2019	h		04/01/2019
6	03/01/2019	w		07/01/2019
7	04/01/2019	h		08/01/2019
8	05/01/2019	w		09/01/2019
9	06/01/2019	w		11/01/2019
10	07/01/2019	h		12/01/2019
11	08/01/2019	h
12	09/01/2019	h
13	10/01/2019	w
14	11/01/2019	h
15	12/01/2019	h
16	13/01/2019	w
Sheet1

Cell Formulas
Range	Formula
J4	=INDEX($G$4:$G$34,AGGREGATE(15,6,(ROW($G$4:$G$34)-ROW($G$4)+1)/($H$4:$H$34=$H$3),ROWS($A$1:$A1)))

Is this how your data looks?

 

[Kirstym1918]

It works for me

Excel 2013/2016

	G	H	I	J
3		H
4	01/01/2019	W		02/01/2019
5	02/01/2019	h		04/01/2019
6	03/01/2019	w		07/01/2019
7	04/01/2019	h		08/01/2019
8	05/01/2019	w		09/01/2019
9	06/01/2019	w		11/01/2019
10	07/01/2019	h		12/01/2019
11	08/01/2019	h
12	09/01/2019	h
13	10/01/2019	w
14	11/01/2019	h
15	12/01/2019	h
16	13/01/2019	w

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula J4 =INDEX($G$4:$G$34,AGGREGATE(15,6,(ROW($G$4:$G$34)-ROW($G$4)+1)/($H$4:$H$34=$H$3),ROWS($A$1:$A1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Is this how your data looks?

Yes pretty much, I'm probably just being a numpty, what data should A1 refer to at the end of the formula?

 

[Fluff]

It isn't referring to anything, it's basically a count, so in J4 it returns1, J5 returns 2 etc.

AGGREGATE(15,6,...,ROWS($A$1:$A1)))
is basically the same as the small function, so the ROWS section on the end is saying the 1st smallest, 2nd smallest etc

 

[Kirstym1918]

It isn't referring to anything, it's basically a count, so in J4 it returns1, J5 returns 2 etc.

AGGREGATE(15,6,...,ROWS($A$1:$A1)))
is basically the same as the small function, so the ROWS section on the end is saying the 1st smallest, 2nd smallest etc

Its now working, helped that I could look how you worked it out.

Thank you very much Fluff

 

[Fluff]

Glad you sorted it & thanks for the feedback