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]

How about
=INDEX($G$4:$G$34,AGGREGATE(15,6,(ROW($G$4:$G$34)-ROW($G$4)+1)/($H$4:$H$34=$J$1),ROWS($A$1:$A1)))

 

[Naveed Ul Haq]

Dear Kirstym1918,

Just unlock your date column from that dollar sign by pressing "F4" twice, mean have a relative reference in your date column and problem solved.

Regards,

 

[Kirstym1918]

Hi,

I've probably not explained properly, the data I have is as follows

01/10/19 W
02/10/19 H
03/10/19 W
04/10/19 H

I have the same INDEX/MATCH formula in 38 vertical cells looking in the above data for any that contain H, if it contains H then it returns the date so the first of the 38 cells returns 02/10/19 but as I dragged the formula down in to all 38 cells, they ALL return 02/10/19 because it is returning the first found match on every single one.

I need to amend the formula so that it returns the first match in cell one, then the second match in cell two, third match cell 3....you get the idea.

What formula do I need to add for this?

Thanks

 

[Fluff]

Did you try the formula in post#2?

 

[Kirstym1918]

Hi Fluff, It was coming up with a NUM error, could you explain what the additional formulas are actually doing to my data? (Yes I am an amateur)

 

[Fluff]

Lets get it working first.
Do you have a H in J1?

 

[Kirstym1918]

Thanks very much Fluff, The H is stored in H3

 

[Fluff]

In that case the formula needs to be
=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)))

 

[Kirstym1918]

In that case the formula needs to be
=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)))

Its not working
Would an IF formula work?, =IF the cell above contains this result then move on to the next H??