If Cell Starts with Text String Formula

Question

I have formula that checks if the cell starts with text "A" returns Pick up "B" for Collect and C for Prepaid.

But it doesn't seems to be working properly

Both A and C returns Prepaid...

=LOOKUP(LEFT(A1),{"A","B","C"},{"Pick Up","Collect","Prepaid"})

Answer 1

The SEARCH function, together with the ISNUMBER function, can be used to see if a cell contains specified text. Substring represents the particular text you're looking for in the generic version, while text represents the text in the cell you're checking. D5's formula is as follows:

=ISNUMBER(SEARCH(C5,B5))
If the substring is discovered, this formula yields TRUE; otherwise, FALSE.