Help with filtering out data

[tjd85]

Hi, above is a works holiday sheet. I want to be able to find out what weeks a specific person appears in. I.e. 'C.Hipkiss' has w/c 'a,b,c & d' off. I've used a VLOOKUP to what find who's off on a specific date but i can't figure out the above problem. Any ideas?

[#2]

@tjd85 

First and foremost you need to be using Excel 365.

FILTER makes it easy to return multiple  values that meet a criterion and the BYROW checks where the selected person appears within the table

= FILTER(weekCommencing, BYROW(holiday = person, OR))

[#3]

Thanks for the reply.

It just returns name! 😕

!

@PeterBartholomew1 

[#4 · Accepted solution]

@tjd85 

@PeterBartholomew1 's formula only works in Excel for Microsoft 365 Insiders.

In the attached workbook you'll find an 'old-fashioned' formula that should work in all versions of Excel. It uses a helper column.

[#5]

Thanks you very much

[#6]

@HansVogelaar 

Using a formula that requires 365 insider was an error on my behalf.  I should have used the Lambda function rather than an eta-reduced version

= FILTER(weekCommencing, BYROW(holiday = person, LAMBDA(x, OR(x))))

 Well done on your formula, BTW.  If you had kicked me, I would have sent you a copy of my workbook rather than having you generate something from scratch.  I had got a reasonable start by using Windows/Shift/S to bring the data in as a picture. 

By the time I had worked your solution through to get

= IFERROR(INDEX(weekCommencing, SMALL(IF(helper, SEQUENCE(12), ""), @k)), "")

I finally understood it!  By now, I have completely lost the knack of reading 'old-style' formulas 😓.

[#7]

Thanks for all the replies and answers. All very helpful. I actually think I'm running 365 on my laptop but the spreadsheet I'm working on is from work which is based on an older version of excel which caused some issues.