named ranges in multiple criteria formula
 

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

named ranges in multiple criteria formula
 

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:

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

named ranges in multiple criteria formula
 

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.



"excelent" wrote:

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:

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

named ranges in multiple criteria formula
 

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:

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.



"excelent" wrote:

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:

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

named ranges in multiple criteria formula
 

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



"excelent" wrote:

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:

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.



"excelent" wrote:

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:

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