Forum Discussion
Solved
Help with filtering out data
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?
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.
- 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.
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))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.
