Multiple IFs Statement: Number to Name

Evan Summerville · 07/30/20

We currently use numerical codes to denote our teams that are synced from JIRA. Those numbers are reflected in a column which I'd like to translate into the relevant team names.

Team Column:

107 = IT-HelpDesk

108 = IT-EnterpriseApplications

109 = IT-CorporateInfrastructure

110 = IT-ProjectManagement

I'd like to create a formula that reads the team column and turns the corresponding number into the team name(in a new column). Something along the lines of:

=IF([Team]:[Team], "107", "IT-HelpDesk", "108", "IT-EnterpriseApplications", "109", "IT-CorporateInfrastructure", "110", "IT-ProjectManagement")

My formula is incorrect, any help is appreciated!

Thanks,

MCorbin · 07/31/20

Personally, I'd create a lookup table.

A separate "Grid" sheet that has 2 columns: Team # and Team Name

Then where you want the team name to show, use an Index/Match formula to lookup the corresponding name based on the the team #

=index({Team Name},Match([Team #]@row,{Team #},0)

Where

{Team Name} is the column in the lookup Grid containing the Team name.

[Team #]@row is the team number that you're pulling from Jira and

{Team #} is the column with the corresponding number in your lookup Grid.

If you want to stick with your formula, try this:

=IF(Team@row="107", "IT-HelpDesk", if(Team@row="108", "IT-EnterpriseApplications",if(Team@row="109", "IT-CorporateInfrastructure", if(Team@row="110", "IT-ProjectManagement"))))

Andrée Starå · 07/31/20

Hi @Evan Summerville

To add to McCorbin’s excellent advice.

You could also add the table to two hidden helper columns in the sheet if you only need it for that sheet.

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

MCorbin · 07/31/20