Trace a number in a range

Lookup a number in a range.
 Trace a number in a number in a table range.

An array formula can help. (Raise your hand if you don't know how to enter an array formula.)

The formula used
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1)-1,0)}
can also be written as:
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1))}

If min and max numbers of all number range always have 2 digits then the formula can be shorten by replacing FIND(" ",$B$2:$B$10)-1 with number 2:
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1)-1,0)}
or
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1))}
 

Attached Images

  

 Courtesy: ANHN

Location Australia

http://www.vbforums.com/showthread.php?534120-Excel-Formula-Number-within-range..-Resolved-and-then-some