named ranges in multiple criteria formula

[Guest]

I'm not sure what formula to use . I have 10 columns of numbers each with
their own name as a heading.

SICU is a named range for room numbers 300-350 .....c2:c51
MICU is a named range for room numbers 400-476......d2:d77
ORTHO is a named range for room numbers 500-583......e2:e84

I want to enter a room number in B2 and have the name of the floor populate A2

So if I enter room number 410 in B2 the answer MICU would fill in A2.

Is this possible?
Thank you in advance for any help you can provide
Ferde

 

[Guest]

put this in F2 and copy down
=C2&" "&D2&" "&E2

put this in A2
=INDEX(C1:E1,MATCH(B2,INDIRECT("C"&MATCH("*"&B2&"*",F1:F84,0)):INDIRECT("E"&MATCH("*"&B2&"*",F1:F84,0)),0))





"ferde" skrev:

 

[Guest]

Thank you so much for the response. This formula works great for A2 . I'm
trying to copy down from A2 but it wont work except in A2. I'm guessing
because of cell reference errors . Since I dont have a clue on how you
figured this formula out... I dont know how to fix it so it will copy down
about 50 rows in column A

Hope this makes sense... thank you for your time and effort.

 

[Guest]

oh ok i just se now u write 10 columns, i was exspected 3
anyway if u paste whitch columns and the range of theese ill try fix it

"ferde" skrev:

 

[Guest]

With a little more work on my part, I figured it out for all 10 columns. My
spreadsheet is working GREAT now .

Thank you so much for the great formula.
Ferde