Setting formula to match dropdown list in columns (same row, same sheet)
Hello,
I need help please.
Is there a way to setup 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.
Answers

@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.

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?

@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.

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!

@Jing Your existing formula would need to go in the [Member Type] column.
Help Article Resources
Categories
Check out the Formula Handbook template!