Referencing value in previous row to assign a contact

Victoria B

On a Requests Sheet, I have a column for Assigned Supervisor. I want to write a column formula that will add a contact based on the previous row's assignment. I'd like to rotate through a list of supervisor contacts each time a form is submitted.

I created a separate Assignment sheet that has a column of the Supervisor and a column Next Assigned Supervisor. I'm trying to write the formula using INDEX and MATCH in the Assigned Supervisor columns of the Requests Sheet that will match the value of the previous row's Assigned Supervisor tp the Assignment sheet's Supervisor Column and return the Next Assigned Supervisor value.

The two areas I am struggling with are:

• How do I reference the previous row in the column formula?
• How do I account for the first row in the column formula?

I'm also open to other approaches.

Danielle Arteaga

Hi, Victoria. Not sure that INDEX/MATCH is going to help you out here. You may need to use VLOOKUP or a nested IF formula, depending on how many Supervisors you have.

Let's say you have 10 supervisors. You could use a nested IF to determine who the next supervisor to be assigned should be. Set up your [Assigned Supervisor] column as a dropdown menu that includes each supervisor's name. Then, create a [Next Assigned Supervisor] column with this column-level formula:

=IF([Assigned Supervisor]@row = "Alice", "Barbara", IF([Assigned Supervisor]@row = "Barbara", "Charlene", IF([Assigned Supervisor]@row = "Charlene", "Dorothy", IF([Assigned Supervisor]@row = "Dorothy", "Edith", IF([Assigned Supervisor]@row = "Edith", "Francine", IF([Assigned Supervisor]@row = "Francine", "Georgia", IF([Assigned Supervisor]@row = "Georgia", "Harriet", IF([Assigned Supervisor]@row = "Harriet", "Irene", IF([Assigned Supervisor]@row = "Irene", "Jessica", "Alice")))))))))

Now, when a name is chosen in the [Assigned Supervisor] column, the [Next Assigned Supervisor] column will automatically select the next supervisor to be assigned based on the logic in your nested IF formula.

If you have a large set of Supervisors and need VLOOKUP guidance, it would be helpful to see the structure of your sheets to suggest a formula.