Calculated Formula for Finding three diferent results

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

• Mike, Both of your solutions resolved the issue. Thank You! – Ronald MacDonald Jan 8 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" )))

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 !! "
)
)
)
• 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 at 14:29
• Sorry to hear that, but great that other solution by Mike Smith worked! – Kristaps Vilerts Jan 8 at 14:34