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