Help with IF statement

[Autoeng]

I can't get this IF statement to return correctly.

I have a field that can contain 4 answers: MP, MP1, MP2, or blank.

If MP return "Not Assigned"
If MP1 return "User 1"
IF MP2 return "User 2"
If blank return blank

=IF(C8="MP1","User 1",IF(C8="MP2","User 2",IF(C8="MP","Not Assigned",""))

With this I am getting a blank return even when C8 contains MP1. What do I need to change?

 

[pbaldy]

There's an error in that formula. This appears to work:

=IF(C8="MP1","User 1",IF(C8="MP2","User 2",IF(C8="MP","Not Assigned","")))

 

[Autoeng]

I still cannot get this to work. You say you had it working?

 

[pbaldy]

Yes, see attached.

 

[Autoeng]

Got it! Now how can I test for more than 7 conditions (MP1, MP2, MP3, ect.)?

 

[Brianwarnock]

I would write a function and use the Switch Function

Public Function fswitch(c As Range)
fswitch = Switch(c = "MP1", "User !", c = "MP2", "User 2", .............)
End Function

then
=fswitch(C8)


Brian

 

[Autoeng]

I am trying a Lookup but getting 50/50 results and can't figure it out.

 

[Brianwarnock]

I like the switch, but a lookup could work and might be easier to maintain.

Its getting late but how are you doing it?

Brian

 

[Autoeng]

=LOOKUP(A1,{"MP ","MP1 ","MP2 ",...},{"User 1","User 2","User 3",...}).

I have to work with "MP#" format as being a 6 character field as it is imported from another source. For some reason MP3, 6, 5, 8, & 9 are all returning the last User instead of the correct one. Now I have to find out why.

 

[Brianwarnock]

Would
=IF(C8="MP","Not Assigned","user "& MID(c8,3,LEN(c8)-2))

work in all cases.

Brian


Ah! just seen your post, not sure if above can be made to work.

 

[Brianwarnock]

I think your syntax is wrong
=LOOKUP(A1,{"MP ","MP1 ","MP2 ",...,;"User 1","User 2","User 3",...}).

Its ; between the 2 sets of references.

brian

 

[Autoeng]

Either works but ; did not solve the error.

 

[Brianwarnock]

Of course its character matches, the array has to be in ascending order therefore MP2 must be after MP19 etc.
Hope this helps

Brian

 

[Brianwarnock]

Hi autoeng how did it go what did you use.
Personally i would use the formula =IF(C8="MP","Not Assigned","user "& MID(c8,3,LEN(c8)-2)) if the sequence is as your example.

If I were to use a Lookup I would use Vlookup as it pairs the target and result values making additions easier, as does the switch.


Brian

 

[Autoeng]

Of course its character matches, the array has to be in ascending order therefore MP2 must be after MP19 etc.
Hope this helps

Brian

That explains it! Who would have thought that? Not me!

 

[Autoeng]

Works perfect BUT (isn't there always a but!) how can I get this to return blank if the cell is blank? I tried """","MP","MP10",...;"""","User1","User10"... but that didn't work.

Any ideas?

And thanks for letting me know arrays had to be in sorted order!

 

[Brianwarnock]

You cannot lookup a null , however just add an If
eg
If(a1="","",Lookup etc

Brian

EDIt BTW my favourite solution to this given fixed length fiels would be

=IF(A2="","",If(A2="MP","NA","user "& RIGHT(A2,4)))

But I would still prefer Vlookup with its paired offerings to Lookup, even though I would be using an extra sheet.
=VLOOKUP(A2,vlookuparray,2)
The array starts in row2
Target Result

MP Na
MP1 User1

etc

 

[Autoeng]

Thanks for the IF. Works perfectly now.