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.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.