named ranges in multiple criteria formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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:
 
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.
 
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:
 
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
 
Back
Top