If Cell Starts with Text String... Formula

As of Excel 2019 you could do this. The "Error" at the end is the default.

SWITCH(LEFT(A1,1), "A", "Pick Up", "B", "Collect", "C", "Prepaid", "Error")

Microsoft Excel Switch Documentation

I know this is a really old post, but I found it in searching for a solution to the same problem. I don't want a nested if-statement, and Switch is apparently newer than the version of Excel I'm using. I figured out what was going wrong with my code, so I figured I'd share here in case it helps someone else.

I remembered that VLOOKUP requires the source table to be sorted alphabetically/numerically for it to work. I was initially trying to do this...

=LOOKUP(LOWER(LEFT($T$3, 1)),  {"s","l","m"}, {-1,1,0})

and it started working when I did this...

=LOOKUP(LOWER(LEFT($T$3, 1)),  {"l","m","s"}, {1,0,-1})

I was initially thinking the last value might turn out to be a default, so I wanted the zero at the last place. That doesn't seem to be the behavior anyway, so I just put the possible matches in order, and it worked.

Edit: As a final note, I see that the example in the original post has letters in alphabetical order, but I imagine the real use case might have been different if the error was happening and the letters A, B, and C were just examples.

I'm not sure lookup is the right formula for this because of multiple arguments. Maybe hlookup or vlookup but these require you to have tables for values. A simple nested series of if does the trick for a small sample size

Try =IF(A1="a","pickup",IF(A1="b","collect",IF(A1="c","prepaid","")))

Now incorporate your left argument

=IF(LEFT(A1,1)="a","pickup",IF(LEFT(A1,1)="b","collect",IF(LEFT(A1,1)="c","prepaid","")))

Also note your usage of left, your argument doesn't specify the number of characters, but a set.

---

7/8/15 - Microsoft KB articles for the above mentioned functions. I don't think there's anything wrong with techonthenet, but I rather link to official sources.

• lookup
• hlookup
• vlookup
• if
• left