I'm trying to pull selections from a list on a source sheet based on multiple criteria on both the source and destination sheets. I can set up the IF formula with JOIN/COLLECT to match one criteria, but struggling using IF(AND(… in order to match multiple criteria.
In my source sheet I have a list of training modules that need to be completed, depending on what level someone is hired at, as well as what time of year they're hired. Here's the formula I'm using:
=IF(AND(Title@row = "Associate", Hiring Season@row = "Summer", {Onboarding Duration Range 6} =1), JOIN(COLLECT({Onboarding Duration Range 1}, {Onboarding Duration Range 5}, HAS(Title@row, @cell)), ", "))
Where Range 6 is the "Summer" column, Range 1 is the "Training" column, and Range 5 is the "Associate" column.
If I remove the AND portion and only look up based on Title, I get all training sessions, regardless of the corresponding season. Hoping there's a way I can pull by both.
