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

User · Accepted Answer

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

User · Answer

As of Excel 2019 you could do this   The  quot Error quot  at the end is the default  SWITCH LEFT A1 1    quot A quot    quot Pick Up quot    quot B quot    quot Collect quot    quot C quot    quot Prepaid quot    quot Error quot    Microsoft Excel Switch Documentation

User · Answer

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       quot s quot   quot l quot   quot m quot     -1 1 0    and it started working when I did this     LOOKUP LOWER LEFT  T 3  1       quot l quot   quot m quot   quot s quot     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

[excel] If Cell Starts with Text String... Formula

Examples related to excel

Examples related to excel-formula