How to Use IF with Multiple Criteria and then to plug in a date - Cross Sheet Reference

haley brianne

I am trying to write a formula that is referencing another sheet. If a row on 2023 Training Request matches the Location Tracker (where im inputting the formula - the studio code is the identifier) AND the Role dropdown on 2023 Training Request is "General Manager" then show the date in the Location Tracker that is in the 2023 Training Request under the column title "HQ Approved Training Date"

=IF(AND([{2023 NSO Training Requests Range 1}] = [Studio Code]@row, {2023 NSO Training Requests Range 2} = "General Manager", {2023 NSO Training Requests Range 3} " ")

Unparseable

Kelly Moore

Hey @haley brianne

One needs to use different types of formulas when looking for data in different sheets. Because your request requires more than one criteria to appropriately filter your data (both the studio code and the General Manager role, the Index/Collect is the correct Index/Collection combo.

=INDEX(COLLECT({2023 NSO Training Requests Range 3}, {2023 NSO Training Requests Range 1}, [Studio Code]@row, {2023 NSO Training Requests Range 2}, "General Manager"),1)

Will this work for you?

Kelly

haley brianne

hi @Kelly Moore it worked!

one question i do have is, is there a way to show "Not Submitted" when there isn't a date for one? It currently just shows #Invalid Value.

Kelly Moore

Hey @haley brianne

Yes. Try this

=IFERROR(INDEX(COLLECT({2023 NSO Training Requests Range 3}, {2023 NSO Training Requests Range 1}, [Studio Code]@row, {2023 NSO Training Requests Range 2}, "General Manager"),1),"Not Submitted")

Kelly