INDEX/MATCH Query - Amendment needed

Kirstym1918

New Member
Joined
Dec 17, 2017
Messages
45
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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)))
 
Upvote 0
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,
 
Upvote 0
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 :)
 
Upvote 0
Did you try the formula in post#2?
 
Upvote 0
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) :)
 
Upvote 0
Lets get it working first.
Do you have a H in J1?
 
Upvote 0
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)))
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top