0

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!

1
  • Mike, Both of your solutions resolved the issue. Thank You! Jan 8, 2019 at 14:35

2 Answers 2

2

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

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

=IF(
    FIND("Andrews",[Tracking Number]),
    "ANDREWS",
    IF(
        FIND("Bolling",[Tracking Number]),
        "BOLLING",
        IF(
            FIND("Pentagon",[Tracking Number]),
            "PENTAGON",
            " !!! NEED VALUE HERE !! "
        )
    )
)
2
  • I appreciate your help. I tried adding the last ELSE part as you suggested and it didn't resolve the issue. Jan 8, 2019 at 14:29
  • Sorry to hear that, but great that other solution by Mike Smith worked! Jan 8, 2019 at 14:34

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.