Using MID w/ IF

[Rembrandt7]

I am trying to use mid with if, I tried nest IF, but that didn't work. I am using the following it work but will not give me the response I need.

=LOOKUP(MID(ROOM_NUMBER,2,1)+0,1,"1st Floor")

ROOM_NUMBER	FLOOR
N1017 <tbody> </tbody>	=LOOKUP(MID(ROOM_NUMBER,2,1)+0,1,"1st Floor")
N1047 <tbody> </tbody>

<tbody>
</tbody>

 

[Scott Huish]

First, is ROOM_NUMBER a named range and second, what are you trying to do exactly?

 

[etaf]

you haven't told us what you want to output

Room_number - is that a named range
or the column

explain the data structure and what output you would like

 

[Scott Huish]

If it is a named range, this is my guess of what you want:
=MID(ROOM_NUMBER,2,1) & LOOKUP(MID(ROOM_NUMBER,2,1)+0,{1,2,3,4},{"st","nd","rd","th"}) & " Floor"

or a littler shorter:

=MID(ROOM_NUMBER,2,1) & CHOOSE(MIN(MID(ROOM_NUMBER,2,1),4),"st","nd","rd","th")&" Floor"