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. Download file link:
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.
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 Excel Spreadsheet Projects
50-Minute (non-public) Video and PDF
When you sign up to the Tiger mailing list:
Also on Insta :-)
0 Comments