How To Match A Number To A Range in Excel

What is the best way to match a number to a range in Excel?

How can you match a number to a range in Excel? It’s a common reason I get hired! Learn how to do it, and avoid the need to ask the IT department, or to hire somebody like me.

Click here to download the Excel file for this video.

Thank you to channel viewer Phillip for sending in this example. Phillip is recording results of physical tests for the military which involves awarding scores for different exercises, including press ups. The scoring system allocates different points for different age groups; so, our spreadsheet must match an age to an age group. Difficult! So many people find themselves trying to do this, and do not know the techniques required.

Match a number to a range in Excel thumb

Phillip’s setup is typical, the age ranges are defined in a cell using upper and lower bounds, and a hyphen, eg. ’17-21′. The problem is that Excel simply cannot make sense of ranges articulated in this way. So, the first step is to express these ranges in a way that Excel understands; this means extracting the lower bounds and listing them in a row.

With this set up, we can think about applying formulae to get the job done. In the video, Chris covers two possible approaches, one using HLOOKUP and the other using MATCH. Both applications are non-typical: HLOOKUP uses approximate match as its 4th component, for example, and MATCH uses ‘less than’ as opposed to ‘exact match’ in its 3rd. Go through the video and work along with Chris to learn how to apply these interesting and useful applications yourself. You will be matching values to ranges in Excel in no time!

Did you manage to match a number to a range in Excel? Let Chris know in the YouTube video comments, he will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
Also find us on Insta

Contact us here.

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.