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")


[TABLE="width: 45"]
<tbody>[TR]
[TD]ROOM_NUMBER[/TD]
[TD]FLOOR[/TD]
[/TR]
[TR]
[TD][TABLE="width: 227"]
<tbody>[TR]
[TD]N1017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=LOOKUP(MID(ROOM_NUMBER,2,1)+0,1,"1st Floor")[/TD]
[/TR]
[TR]
[TD][TABLE="width: 227"]
<tbody>[TR]
[TD]N1047[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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"