Formula that returns a value if the row contains a specific option

Ashley Ann Michele

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.

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