Using IF/AND statements with JOIN/COLLECT

Chrissy P

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.

Melissa Yamada

Hello @Chrissy P

Try adding your Range 6 as additional criteria on your collect function. That would be:

=IF(AND(Title@row = "Associate", Hiring Season@row = "Summer"), JOIN(COLLECT({Onboarding Duration Range 1}, {Onboarding Duration Range 5}, HAS(Title@row, @cell),{Onboarding Duration Range 6},1), ", "))

Melissa Yamada

Hello @Chrissy P

Try adding your Range 6 as additional criteria on your collect function. That would be:

=IF(AND(Title@row = "Associate", Hiring Season@row = "Summer"), JOIN(COLLECT({Onboarding Duration Range 1}, {Onboarding Duration Range 5}, HAS(Title@row, @cell),{Onboarding Duration Range 6},1), ", "))

Chrissy P

Hi @Melissa Yamada

That worked! Thank you!

Melissa Yamada

Happy to help! 😊