Formula that returns a value if the row contains a specific option
Hi!
I could really use some help with creating a formula that will give me a specific output based on a dropdown selection.
I have a column where a county can be selected, and based on the selection, I would like a different column to display a number. The thought process would be if Column County contains A, B, C, or D, display 1 in Column County Number, or if Column County contains E, F, G, or H, display 2 in Column County Number.
Is there any way to accomplish this? I have been looking online, and I cannot find a statement that matches what I am looking for. I would need to group together several counties that can return a value between 1 to 14. Any help to accomplish this would be greatly appreciated.
Answers
-
It could be done using a nested IF statement, but it sounds like you are going to end up with a monster of a formula doing it that way. My suggestion would be to create a reference table in another sheet that has all of the counties in one column and their respective number in another column. Then you can use a formula with cross sheet references like so:
=INDEX({Reference Sheet Number Column}, MATCH([County Column]@row, {Reference Sheet County Column}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!