Listing Options in IF formula
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
Comments
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
Excellent! Happy to help.
Although I don't think I ever saw the part where Samwise ended up in Bedrock...