Multiple IFs Statement: Number to Name

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,

Find more posts tagged with

Formulas

Accepted answers

MCorbin

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å

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!

All comments

MCorbin

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å

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

Evan Summerville

Thanks everyone for your help!

Andrée Starå

@Evan Summerville

Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.