1

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.

1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Sep 9, 2021 at 17:56

1 Answer 1

0

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"))))))))))))))))))))))))))))))))))))))))

1
  • Thank you! It worked.
    – user282399
    Sep 20, 2021 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.