IF/INDEX/MATCH?

Mary Ayers

I'm trying to create a formula for an on-boarding tracking sheet.

-There is one sheet for each new hire. The first row contains info about the new hire, including a column *Position*, which has a single-select dropdown with five position types: "Staff", "Academic Staff", Temp/contingent", "Postdoc" "Faculty".

-Remaining rows are lists of tasks.

-The *Required for Positions* column of the task rows is a multi-select drop down of positions for which that task may or may not be required: "Staff", "Academic Staff", Temp/contingent", "Postdoc" "Faculty".

-I would like to put a formula in the *Status* column that looks and the *Position* cell in Row 1: $[Position$]1 and checks it against the items selected in the *Required for Positions* column. If there is a match, *Status* should default to "Not Started". If there is no match, *Status* should default to "Not Required" (to which I then set up conditional formatting that provides a visual cue that this task is not required for this employee).

I have entered a formula into each of the *Status* cells but that is cumbersome and the formula would have to change each time the requirements change. Here's an example of a task that is required only for "Faculty" or "Staff" positions: =IF(OR($Position$1 = "Faculty", $Position$1 = "Staff", $Position$1 = "Academic Staff"), "Not started", "Not Required")

I know there's got to be better way but I haven't been able to find an example. Hoping one of you formula-gurus can help! Many thanks in advance! 🙂

Genevieve P.

Hi @Mary Ayers,

Since you're using a mutli-select column, you can use the HAS function to search the cell in each row to see if it has the same content as in your Position1 cell. Try this:

=IF(HAS([Required for Positions]@row, $Position$1), "Not started", "Not Required")

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Mary Ayers,

Since you're using a mutli-select column, you can use the HAS function to search the cell in each row to see if it has the same content as in your Position1 cell. Try this:

=IF(HAS([Required for Positions]@row, $Position$1), "Not started", "Not Required")

Let me know if this works for you!

Cheers,

Genevieve

Mary Ayers

Thank you, Genevieve! This worked perfectly! :)

Cheers,

Mary

Genevieve P.

Wonderful! I'm glad this worked for you 🙂