Ms Excel 2016 IF function

Formula:

=IF(C5="Married","M","NM")

You may need to replace the commas with semi-colons:

=IF(C5="Married";"M";"NM")

You're welcome. Thanks for the feedback!

Try this

Formula:

=LOOKUP(8^5;SEARCH({"Married";"Not Married";"Divorced"};C5);{"M";"NM";"P"})

v	B	C	D	E
5	John	Married		M
6	Scott	Not Married		NM
7	Bobby	Divorced		P
8	Scott	Divorced		P

Well, before you embark on writing a formula for a problem, do something that's called "planning".

That will save you and others a lot of time.

So, what options will there be?

- married (M)
- not married (NM)
- divorced (D)
- widowed (W)

Any more? Re-married, maybe? After collecting all the options, it's time to assess which technique to use. With a lot of options, an IF statement can become quite long and hard to maintain, for example with the Name in A1, the married status in B2, the above options in an IF Statement would be

=if(B2="Married","M",
   if(B2="Not Married","NM",
      if(B2="Divorced","D",
         if(B2="Widowed","W","Other")
      )
   )
)

I've used multiple lines and indentation to draw attention to how the IF statements are nested.

With so many options, you may as well write a small table on a different sheet with the married status in column A and the abbreviation in column B. Then you can use a simple Vlookup formula to retrieve the abbreviation:

=vlookup(B2,Sheet2!$A$1:$B:$4,2,false)

To catch errors, wrap it in IFError

=iferror(vlookup(B2,Sheet2!$A$1:$B:$4,2,false),"other")

If you put the lookup table into an Excel object with Insert > table, you can add more rows to the lookup table and the lookup formulas will not need to be changed when new marriage status values are added to the table.

=VLOOKUP(B2,MarriedStatus[#All],2,FALSE)

cheers, teylyn

PS: remember to swap out the commas in the formulas for semicolons if that is what your region uses in Excel.