Use Lookup formula with the MID function

[jeffreybrown]

Hi All,

I have a cell with i.e., 1C571 and based on the 4th number I would like to return a string (in this case it would be TS). Here is the formula I have come up with, but can't seem to get it to work. The MID function pulls the 7 but for the {5,7,9} I am getting {FALSE, FALSE, FALSE}. Maybe there is even a better way?

=IF(OR(MID(D2,4,1)={5,7,9}),LOOKUP(MID(D2,4,1),{5,7,9},{"SS","TS","SMS"}),"")

 

[c_m_s_jr]

***deleted***

 

[c_m_s_jr]

Hi All,

I have a cell with i.e., 1C571 and based on the 4th number I would like to return a string (in this case it would be TS). Here is the formula I have come up with, but can't seem to get it to work. The MID function pulls the 7 but for the {5,7,9} I am getting {FALSE, FALSE, FALSE}. Maybe there is even a better way?

=IF(OR(MID(D2,4,1)={5,7,9}),LOOKUP(MID(D2,4,1),{5,7,9},{"SS","TS","SMS"}),"")

Try wrapping your MID in the function VALUE:

=IF(OR(VALUE(MID(D2,4,1))={5,7,9}),LOOKUP(VALUE(MID(D2,4,1)),{5,7,9},{"SS","TS","SMS"}),"")

When you use LEFT, RIGHT, MID, etc. it converts any number to text. In your original formula you were comparing the text value to a numric value so it will always return false.

 

[Ron Coderre]

You could edit your formula to be this:

=IF(OR(MID(D2,4,1)={"5","7","9"}),LOOKUP(MID(D2,4,1),{"5","7","9"},{"SS","TS","SMS"}),"")

or....this works:

=INDEX({"","SS","TS","SMS"},MAX((MID(D2,4,1)={"5","7","9"})*{1,2,3})+1)

Does that help?

 

[Aladin Akyurek]

Hi All,

I have a cell with i.e., 1C571 and based on the 4th number I would like to return a string (in this case it would be TS). Here is the formula I have come up with, but can't seem to get it to work. The MID function pulls the 7 but for the {5,7,9} I am getting {FALSE, FALSE, FALSE}. Maybe there is even a better way?

=IF(OR(MID(D2,4,1)={5,7,9}),LOOKUP(MID(D2,4,1),{5,7,9},{"SS","TS","SMS"}),"")

Jeff,

Try to coerce the text numbers...

=IF(OR(MID(D2,4,1)+0={5,7,9}),LOOKUP(MID(D2,4,1)+0,{5,7,9},{"SS","TS","SMS"}),"")

 

[jeffreybrown]

Thanks to all. That's what I was forgetting the 4th character was not a number. I went with Ron's formula because all the other's would return #VALUE! where there was not a match.

Again, many thanks...