retrieve column headers when a row contains the same value in multple columns

Hi

I'm struggle with index, match queries to perform the task I require.

Columns are based on date i.e. 1st Jan, 2nd Jan, 3rd Jan etc (say H6:NT6)

Rows are based on name i.e. Joe, Frank, Sandra etc (B6:B23)

I mark off Holidays, Training, Sick days across the rows and use formulas to count totals of each item per person, that is the easy part.

I'm now trying to create a 'Report' sheet, where I can chose the 'name' from someone on sheet1 and it will then give me various bits of data i.e. the dates that they were on holiday, sick

I've looked at various functions but they all seem to only be able to give me the first date that someone had a Holiday and ignore rest.

Is is possible to use {(index,match(index,match))} to acheive this or am i just going to go mad attempting it?, when trying it I was expecting something like '2nd Jan;3rd Jan;7th Mar' but rather got a random number returned (not a julian date type format but something like 33 or 188 depending on which row was used).

Any suggestions would be welcome.

Thank you

Jon

How about attaching a workbook to the thread?

A sample file should be attached.

View attachment sample planner.xlsb

Is this the kind of thing you are chasing?

If so, just copy the formula over to the fields I haven't done yet, change the lookup word (e.g. Holiday), then hold down ctrl+shift while pressing enter.

Perfect, I don't understand it but it works, my backup was fiddling with VBA to attempt it.

Thank you

Beamer said:

If so, just copy the formula over to the fields I haven't done yet, change the lookup word (e.g. Holiday),

Click to expand...

I congratulate @Beamer on the idea.
Maybe this one formula was appropriate. Just copy to the right and down
In B5 cell put this CSE formula