Google Sheets - Return a text value based on another cells text value

Question

I have a set group of "program names" in a column, and want to have another column automatically write the matching "acrononym" for its sister value.

For example: Master in Finance = MAF

However I need a to group all possible cases and their value in a single formula.

Has anyone got an idea of how to work around this? I've tried a bunch of stuff out.

Answer 1

I would recommend Option 1 even though I think you asked for option 2.

Option 1

This is a better approach unless the list of names is very short. I would create a separate helper table that contains all the program names and their associated abbreviations. This can be regularly updated to address common typos and or missed program names without affecting the formulas. The formula can lookup the program name and return the appropriate abbreviation, it can also return a custom error message if no match is found.

Assumptions

1. Helper table has 1st column pre-populated with all long Program Names and range is named 'LongNames'
2. Helper table has 2nd column pre-populated with matching abbreviations and range is named 'AbrvNames'
3. The program names that you want to shorten are in column A (A1:Ax).
4. Your formulas to determine the matching abbreviated names are in column B (B1:Bx).

Your formulas would look like this:

            Column B
   Row 1    =index(AbrvNames,match(A1,LongNames,0),1)
   Row 2    =index(AbrvNames,match(A2,LongNames,0),1)
   Row 3    =index(AbrvNames,match(A3,LongNames,0),1)
   Row 4    =index(AbrvNames,match(A4,LongNames,0),1)
   .
   .
   .
   Row x    =index(AbrvNames,match(Ax,LongNames,0),1)

This would give you the following error message if you lookup a Program Name with a typo or one that isn't in the list:

   #NA

You could put in error handling which would also insure that any additional calculations that key off your formulas don't also error out.

   =IFERROR(index(AbrvNames,match(A1,LongNames,0),1),"!!! Typo or New Program Name !!")

So for any errors you would instead return your custom value.

   !!! Typo or Missing Program !!!

Option 2

This what you requested however unless you have only a handful of Names it will be difficult to manage and inefficient.

Assumptions

1. You have all the the possible values stored in the formula itself.
2. You have program names that you want to shorten in column A (A1:Ax).
3. Your formulas to determine the matching abbreviated names are in column B (B1:Bx).
4. I used "Master of Finance" and "MAF" as placeholders for the Long Program Names and the Abbreviated names in the example below and modified them with a number for simplicity but you would use the real English words.

Formula

You would string together conditional statements (one for each pair of related names) until you have exhausted all possible combinations. When you see how unwieldy this becomes keep in mind if you want to include variations for the same degree, or common typos. All of which is easily handled in Option 1 above.

Example 1
1 Program Name to Check For

=IF(A1="Master of Applied Finance001","MAF001","Nothing_Found")

=IF(A2="Master of Applied Finance001","MAF001","Nothing_Found")
.
.
.
=IF(A?="Master of Applied Finance001","MAF001",)

Example 2
5 Program Names to Check For

=IF(A1="Master of Applied Finance001","MAF001",IF(A1="Master of Applied Finance002","MAF002",IF(A1="Master of Applied Finance003","MAF003",IF(A1="Master of Applied Finance004","MAF004",IF(A1="Master of Applied Finance005","MAF005","Nothing_Found")))))

=IF(A2="Master of Applied Finance001","MAF001",IF(A2="Master of Applied Finance002","MAF002",IF(A2="Master of Applied Finance003","MAF003",IF(A2="Master of Applied Finance004","MAF004",IF(A2="Master of Applied Finance005","MAF005","Nothing_Found")))))
.
.
.
=IF(A?="Master of Applied Finance001","MAF001",IF(A?="Master of Applied Finance002","MAF002",IF(A?="Master of Applied Finance003","MAF003",IF(A?="Master of Applied Finance004","MAF004",IF(A?="Master of Applied Finance005","MAF005","Nothing_Found")))))

Example 3
20 Program Names to Check For

=IF(A1="Master of Applied Finance001","MAF001",IF(A1="Master of Applied Finance002","MAF002",IF(A1="Master of Applied Finance003","MAF003",IF(A1="Master of Applied Finance004","MAF004",IF(A1="Master of Applied Finance005","MAF005",IF(A1="Master of Applied Finance006","MAF006",IF(A1="Master of Applied Finance007","MAF007",IF(A1="Master of Applied Finance008","MAF008",IF(A1="Master of Applied Finance009","MAF009",IF(A1="Master of Applied Finance010","MAF010",IF(A1="Master of Applied Finance011","MAF011",IF(A1="Master of Applied Finance012","MAF012",IF(A1="Master of Applied Finance013","MAF013",IF(A1="Master of Applied Finance014","MAF014",IF(A1="Master of Applied Finance015","MAF015",IF(A1="Master of Applied Finance016","MAF016",IF(A1="Master of Applied Finance017","MAF017",IF(A1="Master of Applied Finance018","MAF018",IF(A1="Master of Applied Finance019","MAF019",IF(A1="Master of Applied Finance020","MAF020","Nothing_Found"))))))))))))))))))))

Example 4
40 Program Names to Check For

=IF(A1="Master of Applied Finance001","MAF001",IF(A1="Master of Applied Finance002","MAF002",IF(A1="Master of Applied Finance003","MAF003",IF(A1="Master of Applied Finance004","MAF004",IF(A1="Master of Applied Finance005","MAF005",IF(A1="Master of Applied Finance006","MAF006",IF(A1="Master of Applied Finance007","MAF007",IF(A1="Master of Applied Finance008","MAF008",IF(A1="Master of Applied Finance009","MAF009",IF(A1="Master of Applied Finance010","MAF010",IF(A1="Master of Applied Finance011","MAF011",IF(A1="Master of Applied Finance012","MAF012",IF(A1="Master of Applied Finance013","MAF013",IF(A1="Master of Applied Finance014","MAF014",IF(A1="Master of Applied Finance015","MAF015",IF(A1="Master of Applied Finance016","MAF016",IF(A1="Master of Applied Finance017","MAF017",IF(A1="Master of Applied Finance018","MAF018",IF(A1="Master of Applied Finance019","MAF019",IF(A1="Master of Applied Finance020","MAF020",IF(A1="Master of Applied Finance021","MAF021",IF(A1="Master of Applied Finance022","MAF022",IF(A1="Master of Applied Finance023","MAF023",IF(A1="Master of Applied Finance024","MAF024",IF(A1="Master of Applied Finance025","MAF025",IF(A1="Master of Applied Finance026","MAF026",IF(A1="Master of Applied Finance027","MAF027",IF(A1="Master of Applied Finance028","MAF028",IF(A1="Master of Applied Finance029","MAF029",IF(A1="Master of Applied Finance030","MAF030",IF(A1="Master of Applied Finance031","MAF031",IF(A1="Master of Applied Finance032","MAF032",IF(A1="Master of Applied Finance033","MAF033",IF(A1="Master of Applied Finance034","MAF034",IF(A1="Master of Applied Finance035","MAF035",IF(A1="Master of Applied Finance036","MAF036",IF(A1="Master of Applied Finance037","MAF037",IF(A1="Master of Applied Finance038","MAF038",IF(A1="Master of Applied Finance039","MAF039",IF(A1="Master of Applied Finance040","MAF040","Nothing_Found"))))))))))))))))))))))))))))))))))))))))