Listing Options in IF formula

Stephanie Hadley

I've looked at the forums and tried as many variations in the formula as I can think of, so I need some help. 

My two columns (both drop downs):

Payee Name

Payee Position *(location of formula)

I'm trying to get Payee Position to automate the position title for each payee. Here's what I've got to work for one position title:

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist")   

But I've got other Payee Names that are not just Benefit Strategists...I've also got Account Executives and Production Partners. How would I include those?? I've thought:

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist"), IF(OR([Payee Name]2 = "Fred Flinstone", [Payee Name]2 = "Mike Miller", [Payee Name]2 = "Samwise Gamgee"), "Account Executive")  

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist" OR([Payee Name]2 = "Fred Flinstone", [Payee Name]2 = "Mike Miller", [Payee Name]2 = "Samwise Gamgee"), "Account Executive")  

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist"), =IF(OR([Payee Name]2 = "Fred Flinstone", [Payee Name]2 = "Mike Miller", [Payee Name]2 = "Samwise Gamgee"), "Account Executive")  

None of those have worked, so I am really needing some help!

Thanks a bunch!

-Stephanie

 

 

Paul Newcome

I would recommend building a table with all of the names in one column and the corresponding titles in another. You could then use an INDEX/MATCH. This will add the benefit of flexibility to add or remove names and/or change titles without having to update tedious, long IF statements. A table like below (column names in bold)(Table column names do NOT have a space in them for this example):

 

Payee Name    Payee Position                  NameTable          TitleTable

 Mike                 Formula                              John                     CEO

 Jane                 Formula                              Mike                     CFO

 Janet                Formula                              Jane                     Pres

 John                 Formula                              Janet                    Intern

 

Would use a formula like this:

 

=INDEX(TitleTable:TitleTable, MATCH([Payee Name]@row, NameTable:NameTable, 0))

 

What this does is looks at the name you have in the the Payee Name column. It will then search the table for that name and input whatever title is next to it.

 

Much easier and more flexible than convoluted IF statements.

 

Hope this helps.

 

P.S.

You can also build the table on a separate sheet and use cross sheet references to search the table. Then you can have separate sharing permissions for the sheet and the table (limits people "accidentally" messing something up)

Stephanie Hadley

 Thank you!! This works PERFECTLY! I ended up doing a separate sheet like you said. Thanks so much for the help!

 

-Stephanie

Paul Newcome

Excellent! Happy to help. 

 

Although I don't think I ever saw the part where Samwise ended up in Bedrock...