Use INDEX function to look for nth match

[DiepieBUX]

Currently I am building an automated planning sheet which makes use of multiple worksheets. There is an overall sheet which pulls data from the project list based on the INDEX-MATCH formula.

The problem now is, as I put two equal values (names) in the project lists beneath each other, it always pulls out the first match and does not look beyond that first match if first match is not correct for another correct match below the first given match. The match is based on the name and if it is in between the start- and end date of the given project.

In the given Excel the problem is visualized. In sheet 'Projectenlijst', there's given two project for the first worker except within the sheet 'Overzichtsplanning' it only shows the first given match and not the second match.

Is there any possible way for the INDEX-MATCH formula to continue searching until first found match is true?

[FDibbins]

With regular INDEX/MATCH, thats the way it works...it finds the 1st match then stops looking.

How would excel know that you want the 2nd match, and not the 1st?

[DiepieBUX]

Hello, thanks for your reply.

The way I want this working is that the INDEX only returns the value if there is a match, in my case is the date is in between the given dates in the 'projectenlijst'. If this match is not there it should continue looking down whenever there is a correct match and return that value, instead of doing nothing as the first index lookup wasn't a match.

[JeteMc]

The following formula yields "Projectnumber_2 / Projectname_2" for Sept. 7th through 11th which I assume is the desired output:

Please Login or Register  to view this content.

Note that the above formula removes the reference to the Verlof & ziektelijst sheet so we probably need to see some data on that sheet to know how that section should be replaced.
Note that the formula in the post may not work for your regional settings, however the formula in cell C4 of the Overzichtsplanning sheet should.
Let us know if you have any questions.

[DiepieBUX]

Hello,

My sincere apologies for my late reply, but this worked like a charm! Thank you very much.
I can build the 'Verlof & ziektelijst' to this, it basically works the same except it has an additional index-match with the time stamp.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.