I am working on a calculated formula based on my [Tracking Number] column to get the following results:

  1. Find "Andrews" return the result "ANDREWS".
  2. Find "Bolling" return the result "BOLLING".
  3. Find "Pentagon" return the result "PENTAGON".

The formula that I have come up with returns "ANDREWS" when it finds "Andrews", and returns #VALUE! for all the others.

This is the formula that I am using. Please help me resolve this issue.

=IF(FIND("Andrews",[Tracking Number]),"ANDREWS",IF(FIND("Bolling",[Tracking Number]),"BOLLING",IF(FIND("Pentagon",[Tracking Number]),"PENTAGON")))

Thanks in advance!

  • Mike, Both of your solutions resolved the issue. Thank You! – Ronald MacDonald Jan 8 '19 at 14:35

The FIND function is not real friendly… if there is not a match it returns an error. Here's one solution:

=IF( NOT(ISERR(FIND("Andrews",[Tracking Number]))), "ANDREWS",
      IF( NOT(ISERR(FIND("Bolling",[Tracking Number]))), "BOLLING",
         IF( NOT(ISERR(FIND("Pentagon",[Tracking Number]))), "PENTAGON", "none" )))

Here's an alternate solution:

=IF( ISNUMBER(FIND("Andrews",[Tracking Number])), "ANDREWS",
      IF( ISNUMBER(FIND("Bolling",[Tracking Number])), "BOLLING",
         IF( ISNUMBER(FIND("Pentagon",[Tracking Number])), "PENTAGON", "none" )))
| improve this answer | |

Seems like You are missing last ELSE part in third third IF statement

    FIND("Andrews",[Tracking Number]),
        FIND("Bolling",[Tracking Number]),
            FIND("Pentagon",[Tracking Number]),
            " !!! NEED VALUE HERE !! "
| improve this answer | |
  • I appreciate your help. I tried adding the last ELSE part as you suggested and it didn't resolve the issue. – Ronald MacDonald Jan 8 '19 at 14:29
  • Sorry to hear that, but great that other solution by Mike Smith worked! – Kristaps Vilerts Jan 8 '19 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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