Need Help with Complicated IF Statement with Many Variables

This is a complicated thing I'm trying to accomplish and I only have a rudimentary grasp of Excel formulas and logical statements, so your help would be greatly appreciated!

I need to check BE2 if it's blank. If it's not blank, leave it alone. If it is blank, I need to check S2. If S2 contains the name of 1 of 11 counties (ex.: "Marion","Pinellas","Orange","Osceola", etc.), I need BE2 to say "TBD". If it does not say one of those 11 counties, it needs to say "N/A".

Can this even be done? If so, could someone PLEASE write an example?

Thank you!

Try this

=IF(BE2="","",IF(OR(S2={"Marion","Pinellas","Orange","Osceola","etc"}),"TBD","N/A"))

add some more county names

The other way... maybe the long way is to use if functions

=IF(ISBLANK(C2),IF(K2="Marion","TBD", IF(K2="Pinellas","TDA",IF(K2="Orange","TDA","n/A"))),C2)

I have detailed explanation of if statements

http://easyexcelanswers.com/func_for...#IF Statements

There should not be any Circular dependency unless you entered formula in BE2

{"Marion","Pinellas","Orange","Osceola","Hillsborough","Lake","Palm Beach","Polk","Seminole","Sumter","Pasco"}


You realize the curly brackets around these values indicate that it is an array.

To get them you must press ctrl - shift - enter, you are not able to key them in yourself

Your formula is fine as written (for Excel, anyway), and can be confirmed in the normal way. S2 must not contain any spurious characters, like a trailing space.

Also, (sorry), make sure you're entering the value into the right cell.

@easyexcelanswers

Please note that curly brackets do not always define a formula as an array. They are also used to accommodate multiple criteria in regular formulas and therefore do not require Ctrl+Shift+Enter key confirmation.

@loriahein

Please see attached Excel file with formula.

Originally Posted by loriahein

I copied your formula and pasted it where it needs to go, hit enter, and it still says N/A even though S2 is Pasco, which is in the list. I don't understand what I'm doing wrong? Sorry, extreme newbie here!

Could you upload your spreadsheet? I suspect the problem is with your data. Make sure there are no leading or trailing spaces in S2 or at the source where the data is coming from. BTW, you have to use Excel not Google Docs.

I've never used Google docs for spreadsheet. But I am gonna try it and see what can be done.

See if you can do this by creating a lookup table in Google Docs:

Then use this formula. I did test it in Google Docs spreadsheet and it worked fine.

=IF(ISNA(VLOOKUP(S2,T2:T12,1,0)),"N/A","TBD")

	Q	R	S	T	U
1	Result		County		Lookup table
2	TBD		Orange		Marion
3					Pinellas
4					Orange
5					Osceola
6					Hillsborough
7					Lake
8					Palm Beach
9					Polk
10					Seminole
11					Sumter
12					Pasco

You're welcome!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).