Setting formula to match dropdown list in columns (same row, same sheet)

Jing

Hello,

I need help please.

Is there a way to set-up a formula so that an item in a column with dropdown list will automatically select an item in another column with a dropdown list?

For example, Column A has 1,2,3 in dropdown and Column B has 4,5,6 in its dropdown list. If 1 is selected in Column A, it will automatically match with 4 in Column B.

sharkasits

@Jing Is it always a one to one match or do you sometimes want the user to be able to select something specific in Column B?

If it's a one to one match, you can create a formula in Column B that looks at Column A. If there are a lot of choices, I would create a helper lookup table.

If you want the person to be able to change Column B, you use an automation, but then you need a path for each value you want automatically selected in Column B.

I hope that makes sense. If either of these works for what you are trying to do and you need help on the details, let me know.

Jing

Hi @sharkasits,

Thank you for taking the time to answer my question. Yes, indeed. It's a one to one match. The choices is only maximum of 5. can you please let me know the step by step process how to create the formula?

sharkasits

@Jing If it's only 5, you can write a formula in column B:

=IF([Column A]@row = <<value 1>>,<<coresponding Column B value 1>>,IF([Column A]@row = <<value 2>>,<<coresponding Column B value 2>>,IF([Column A]@row = <<value 3>>,<<coresponding Column B value 3>>,IF([Column A]@row = <<value 4>>,<<coresponding Column B value 4>>,IF([Column A]@row = <<value 5>>,<<coresponding Column B value 5>>,"")))))

Just replace everything between each set of <<value>> with your values.

Jing

Hi @sharkasits,

Thanks so much for your guidance. I have tried the formula (please see image below) but I am getting a "CIRCULAR" notification.

Please advise. Thank you!

Paul Newcome

@Jing Your existing formula would need to go in the [Member Type] column.