Nested If(Search

[Celticfc]

Hi,

In Column N,

I have a drop down with 7 options:

Later
Next 1
Next 2
Next 33
Future 3
Future 4
Future 43

In column M, I want to categorise them in 3 options = Later, Next and Future but I’m getting value error.

This is what I tried:
=if(search(“later”,n2),”later”,””)&if(search(“next”,n2),”next”,””)&if(search(“future”,n2),”future”,””)

 

[Eric W]

Try:

=LOOKUP(LEFT(N2),{"F","L","N"},{"Future","Later","Next"})

 

[Jonmo1]

How about

=LEFT(N2,FIND(" ",N2)-1)

 

[Celticfc]

Try:

=LOOKUP(LEFT(N2),{"F","L","N"},{"Future","Later","Next"})

Thank you, this gave me NA error.

By the way there's a dash between text and digits e.g. A Future - 43, B Future - 3, B Pro 1 etc. Could this be the NA reason?

 

[Eric W]

It'll give you an N/A error if N2 is empty, or if it starts with something other than F, L or N. The dash shouldn't matter. Jonmo's formula should work as long as there's a space after the first word.

 

[Celticfc]

How about

=LEFT(N2,FIND(" ",N2)-1)

Hi Jonmo1,

I had to amend my dropdown list and been trying to find a way to sort into Now, Pro and Future categories but no luck.

Can you help me with the below? Thank you.

A - now
A - pro 1
A - pro 2
A - pro 3
B - now
B - pro 1
B - pro 2
B - pro 3
C - future 4
C - future 5

 

[Eric W]

2 options:

=IFERROR(LOOKUP(MID(N2,5,1),{"f","n","p"},{"Future","Now","Pro"}),"")

=TRIM(MID(SUBSTITUTE(N2," ",REPT(" ",20)),42,8))

 

[Jonmo1]

Try

LEFT(N2,FIND(" ",N2&" ")-1)