Search This Blog

March 7, 2013

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:
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1))}
Attached Images   
 Courtesy: ANHN
Location Australia

No comments:

Post a Comment