Help Using INDEX MATCH to Return Multiple Results with Dynamic Values

[FONT=Arial]Hi everyone,

My work requires me to report the dates and hours which we provided services to clients in order to claim payment. The spreadsheet I use is formatted by the funding body, which I have reproduced a simplified version of for reference (see: attached). I am trying to find a function to extract the data from our service records and populate them in the funding body?s template, as the current process is very laborious and prone to human error. Ideally, I would be using a function on the funding body?s spreadsheet to pull the internal data from another spreadsheet or sheet but for this example I have collated both onto a single sheet in the one spreadsheet to make things easier.

I initially tried using VLOOKUP to achieve return the results I need, but as VLOOKUP only returns the first result which matches the lookup value, I am unable to capture additional matches (of which there can be many). My best effort so far employed the INDEX and MATCH functions, but it cannot run dynamically, so as soon as I drag the function to a new lookup value, it returns no results. I have included this attempt in the example.

Some people recommend the FILTER and SORT functions as an alternative to INDEX MATCH, but since the version of Excel I am using is 2016 these are unavailable to me.

Please let me know if you have any questions!

Kind regards,
preppy_princepreppy_prince_Example_18052023.xlsx

E2=IFERROR(INDEX($C$2:$C$13,AGGREGATE(15,6,ROW($C$2:$C$13)-ROW($C$2)+1/($B$2:$B$13=$D2),COUNTIF($D$2:D2,D2))),"")

Copy down

Hi CARACALLA,

Thank you so much - your solution worked like magic!