Finding a value within a named range and returning that named range to another cell

[divott]

I have ~150 sets of data like this, going across and down the page. They are drivers job runs named M01, M02 etc and under them are the site numbers/names.
Each set of data is a named ranged, called M01, M02, M03 etc.

What I need is on another worksheet, to lookup the site number (e.g. 9811, or 9812 etc) and return the job run name (the named range).

Thanks in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD]M01[/TD]
[TD][/TD]
[TD]M02[/TD]
[TD][/TD]
[TD]M03[/TD]
[/TR]
[TR]
[TD]9811 Eastgardens[/TD]
[TD][/TD]
[TD]7308 Maroubra[/TD]
[TD][/TD]
[TD]7192 Bondi Junction[/TD]
[/TR]
[TR]
[TD]9812 Maroubra (Z)[/TD]
[TD][/TD]
[TD]9815 Randwick[/TD]
[TD][/TD]
[TD]7288 Woolahra[/TD]
[/TR]
[TR]
[TD]9813 Maroubra (Z)(B)(N)[/TD]
[TD][/TD]
[TD]7285 Randwick[/TD]
[TD][/TD]
[TD]7151 Paddington[/TD]
[/TR]
[TR]
[TD]9814 Maroubra (Z)(B)(S)[/TD]
[TD][/TD]
[TD]7199 Randwick[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M14[/TD]
[TD][/TD]
[TD]M15[/TD]
[TD][/TD]
[TD]M16[/TD]
[/TR]
[TR]
[TD]7251 Leichhardt[/TD]
[TD][/TD]
[TD]7127 Villawood[/TD]
[TD][/TD]
[TD]7249 Parramatta[/TD]
[/TR]
[TR]
[TD]7240 Haberfield[/TD]
[TD][/TD]
[TD]7128 Lansdowne[/TD]
[TD][/TD]
[TD]7282 Parramatta[/TD]
[/TR]
[TR]
[TD]7241 Five Dock[/TD]
[TD][/TD]
[TD]9546 Lansvale[/TD]
[TD][/TD]
[TD]7107 Parramatta[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7333 Parramatta[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hatstand]

Hi,

Is it basically a search box. Enter "9811" and it will return "Eastgardens" ? or do you need it to list all of the available numbers to search?

 

[divott]

Hi, thanks for your quick reply.
What I want is to search for 9811 and return what named range it is in i.e. M01 or 7127 and return M15.
Thanks