Using multiple IF/Left Function

C. Perry

I have 3 column headers which are name, subsidiary and reviewer. Based on the first letter of the name column and the full name of the subsidiary column I want to assign a team member to the reviewer column. The first team member will be responsible for all name letters starting with A-L for subsidiary 1, the second team member will cover M-Z for subsidiary 1 the 3rd team member will cover all of subsidiary 2 from A-Z. I can create a formula for each letter but want one formula to do it for all letters from A-L, M-Z and A-Z for all subsidiaries. I need one formula that combine all three reviewers from A-Z by subsidiary.

The formula I started with but need to expand it:

=IF(LEFT([Full Name]@row, 1) = "M", IF([Subsidiary]@row = "Band", "TomReviewer"))

Paul Newcome

Are you able to provide screenshots for reference?

C. Perry

Good afternoon Paul. Please see image. The formula 5 am using that returns the reviewer is =IF(LEFT([Full Name]@row, 1) = "a", IF(Subsidiary@row = "RBA", "Perry")). For each viewer I change from Perry to Liza/Joe. I need one formula that will determine the reviewer based on first letter full name and full subsidiary name. Perry is A-L, Joe is M-Z and Liza is A-Z.

C. Perry

The formula I am using that returns the reviewer is =IF(LEFT([Full Name]@row, 1) = "a", IF(Subsidiary@row = "RBA", "Perry"))

Paul Newcome

I would suggest a helper column with a nested IF or an INDEX/MATCH to a separate reference table that converts the first letter into a number. The nested IF would be rather lengthy but easy enough to manage since it wouldn't change.

Then to output the name you could use something more like

=IF(Subsidiary@row = "RBA", IF(Helper@row<= 12, "Perry", "Joe"), "Liza")

C. Perry

Can you provide an example of what the nested IF or an INDEX/MATCH would look like. Also I have a total of 6 subsidiaries on my report but only need to associate two of them with the reviewers. Also the IF(Helper@row<= 12 if I create the helper nested column would this still be able to provide a distinction of the reviewer Perry A-L and Joe M-Z once converted to numbers. Would I still need to do a nested for =IF(Subsidiary@row = "RBA", IF(Helper@row<= 12, "Perry", "Joe"), "Liza") to capture A-L and separate M-Z? If so please provide an example.

Paul Newcome

The nested IF would be along the lines of:

=IF(LEFT([Full Name]@row) = "A", 1, IF(LEFT([Full Name]@row) = "B", 2, IF(LEFT([Full Name]@row) = "C", 3, IF(LEFT([Full Name]@row) = "D", 4, ...........................

This will output a number based on the first letter in the [Full name]@row cell. The IF in my last comment leverages that number output by the helper column to basically say that "if the first letter in the [Full Name]@row cell is one of the first 12 letters in the alphabet".

C. Perry

Your response has a lot of moving parts. Is there a more simple way of doing a single formula that states = if subsidiary is RBA and the first letter of the full name is A thru L put Perry, if subsidiary is RBA and the first letter of the full name is M thru Z put Joe, if subsidiary is RBAS and the first letter of the full name is A thru Z put Liza.

Paul Newcome

There are only two moving parts.

First you create a helper column with a nested IF to convert the first letter into a number. An example has been started in my last comment.

The second part is the formula that outputs the name of which an example is provided in my comment from earlier (2nd to last comment).

Letters are text strings, so they cannot be compared as greater than or less than another letter. That is why we have to convert the first letter into a number. Otherwise you would need to create a more complicated nested IF repeatedly stripping the first letter (similar to my response) but then comparing that to each letter of the alphabet individually.