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
H
W
h
w
h
w
w
h
h
h
w
h
h
w

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]02/01/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]02/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]04/01/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]03/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]07/01/2019[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]04/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]08/01/2019[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]05/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]09/01/2019[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]06/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]11/01/2019[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]07/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12/01/2019[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]08/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]09/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]10/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]11/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]12/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]13/01/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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