Can you use an If...Then Formula to populate multiple columns?

Nicole Davis

Hi!

I am looking to create a formula that will auto populate three columns depending on what region is chosen.

For Example:

If the region chosen is the Northeast then I want Person 1, 2 & 3 to auto populate.

If the region chosen is the Mid-Atlantic then I want Person 1, 4 & 5 to populate...and so on.

Is this possible? I tried the vlookup but it was too row specific.

Thank you!

Paul Newcome

Do you have the people listed out in a grid that can be referenced somewhere?

bcwilson.ca

@Nicole Davis I did exactly what @Paul Newcome suggested.

I use something similar for purchase approvals to route based on the department that created the request

1. Simply create a Matrix, let's call it "Notification Matrix" and define Person 1, 2 3 for each region.
   1. Make sure those columns are set up as "Contact Lists" in both the Matrix and in the Sheet
2. Do a vlookup for your SD VP from the Matrix using the Region as your look up
   1. something like "=VLOOKUP([Region]@row, {Notification Matrix Range 1}, 2, false)"
   2. Repeat for 2 and 3 to get your Director and RVP
3. Right-click and make those Column Formulas
   1. also, lock the cells so no one can modify the formulas

And you are done..

You can now use workflow rules with those contacts as well..

So if you get a big order in the Southeast you could have a rule to Email the RVP based on dollar value, etc.

Or if an invoice remains unpaid for X many days it could use workflow to escalate using the contacts

If you are not using it for workflow they can simply be text fields

Nicole Davis

@bcwilson.ca & @Paul Newcome Thank you both for the very quick suggestion. I did have a table, however deleted it when I thought I was on the incorrect path. I will go back in and try this!