Need formula to return phrase based upon drop down choice

[Mojo77]

Ok guys, I've been trying to get this right all day, but so far can't figure it out. I have a sheet I'm trying to make for work. We base what we can do in certain things based on what state an account is based in. I'm trying to make a sheet where we input the state in one cell and the next cell over shows the result. Example: A2 has a drop down menu of all 50 states. Based on that A3 shows one of several phrases. It should be simple, but I cna't figure it out. I've been using this formula: =IF(A2="CO", "Yes in all cases") and it works fine if you select CO, but if you select another state it just shows "False". I need to be able to add more states for that phrase. Plus I need to use other formulas in the same cell that result in different phrases for a set of other states. Can anyone figure this out?

[Pete_UK]

Presumably you have a list of your 50 states somewhere, so you can easily add in the adjacent column the phrase that you want to generate for each state. Then you will be able to use a VLOOKUP formula to get the appropriate phrase.

I can't give you the formula as you haven't told us anything about how your data is laid out and which cells you are using, so you should attach a sample workbook as described in the yellow banner at the top of the screen.

Hope this helps.

Pete

[TMS]

Here is a sample file that builds on the solution outlined by Pete.

You could change the Traditional Abbreviation column to have the comments for each state. Or, you could add another column.

Adjust Sheets, Ranges, etc., as required.

[Mojo77]

Sheet.png

What I'm wanting would look like this. A2 has a drop down menu of the abbreviations of all 50 states. Once one is selected then a message will appear in B2. There are about 7 different messages depending on which state is selected.

[TMS]

Ok, let's work together on this. You outlined a problem. Pete described a possible solution based on little detail. I built on that and provided a sample file. Gut reaction is, it's your turn ... but, no, here's the example updated.

If you can't work with this ... Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[FlameRetired]

Mojo77 welcome to the forum.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Since you are new to the forum I will changed it for you this time.

[Mojo77]

Ok, I attatched what I have so far. Basically when a state is selected in A2, depending on what state was selected one of 5 messages should pop up in B2.

These are the states and messages:

CO,GA, HI, IL, IN, LA, MA, MD, ME, MI, MN, MT, MO, NC, NE, NH, NJ, NY, OH, OR, PA, RI, TX, VA, VT, WA, WY Yes in all cases

AZ, CA, FL, IA, ID, KY, MS, ND, SC, UT, WI, AR No when titling is "OR", Yes in all other cases

CT No when titling is "OR" or blank, Yes in all other cases

DE No when titling is "And/Or", yes in all other cases

AL, KS, NM, NV, SD, TN, WV No when titling is "OR" or "And/Or", Yes in all other cases

[TMS]

I have no idea what "when titling" means.

I think it's time you joined in and did some of the practical stuff for yourself. I've built a working example. All you need to do is fill in the relevant messages. I have no plans to do this for you. This is a help forum, not a "do it all for you" forum.

I think I’m done here, at least for now. If you come back with the workbook set up, the table completed, named ranges established, data validation set up, lookup formula in place, etc., and still need help, I may reconsider. Please bear in mind that that has already been done for you in the sample I uploaded. That might be a good place to start rather than build it from scratch.

[Mojo77]

I have no idea what "when titling" means.

I think it's time you joined in and did some of the practical stuff for yourself. I've built a working example. All you need to do is fill in the relevant messages. I have no plans to do this for you. This is a help forum, not a "do it all for you" forum.

I think I’m done here, at least for now. If you come back with the workbook set up, the table completed, named ranges established, data validation set up, lookup formula in place, etc., and still need help, I may reconsider. Please bear in mind that that has already been done for you in the sample I uploaded. That might be a good place to start rather than build it from scratch.

I have no intention of you doing it for me, I just need to figure out a way to make this happen. And I'm sure you don't know what half of the terminology means, but that's irrelivent. I just need it to state that in the cell. It should be a simple formula, but I can't get it right. I can partially make a formula: =IF(A2="CO"="Yes in all cases") this works for one state, but I'm not sure how to make that work for multipule states. Plus I also want to add formulas for the other state groups as well in the same cell. I know it can be done, because it was on another sheet that someone deleted.

[TMS]

You need to upload a representative sample workbook, not an example of a trivial Data Validation drop down list. I/we cannot help you work out what the formula needs to be with the great majority of the data/information missing. It clearly isn't a simple INDEX/MATCH, but I doubt it will be an IF statement either.

[Mojo77]

Not sure how I can do that since I have no idea how. That's kind of the problem. I'm stuck with it and I'm not sure how to move forward with it. I guess I was probably wrong to come here expecting any help. It's cool man, don't worry about it.

[TMS]

You must have more in your workbook than a drop down DV list. You weren't wrong to come here looking for help but YOU need to help US to help YOU.

If some of the information is sensitive, redact it or delete it. But share the bits we need to help you build a formula. Like "titling". Where does that come from? What does it mean? Is it part of the input?

CT No when titling is "OR" or blank, Yes in all other cases

DE No when titling is "And/Or", yes in all other cases

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Assuming the cell with "titling" is cell C2.