Returning specific value based on mulitple 'ranges' and variables?
Posted on 2011-10-26
Have a tough one that I think I'm going to need help with! How can a set of ranges (arbitrary alphanumeric ranges within a range of cells) which have a specific column identifier be found based on two user variable inputs?
To make that a bit clearer (hopefully!), there needs to be manual input of an age range (for example '30-39', preferably from a drop-down list) and a numeric value between upper and lower limits (for example, 1000-2000); let's say these are in Column A and B respectively.
The A value has 100 different alphanumeric 'ranges' assigned, which are divisions of the range withn the upper and lower limits of the B value, e.g. 1000-1009, 1010-1019, 1020-1029, etc. Each of these ranges has an associated 'score', from 1 to 100. Each A value will have different upper and lower limits (e.g. 20-29 might be 1500-3000), but always the same number of divisions (100, and therefore the same 1 to 100 'scoring').
The formula I need to create will look at the A and B values and based on them, reveal the 'score' (between 1 and 100) for that combination of age range (A) and numeric value (B), based on which of the 100 ranged divisions the numeric B vlaue falls into.
Confused? Yeah, me too. :)
I thought this might be possible via a combination of HLOOKUPS and VLOOKUPS, but having gone through it in my head (not really tried it in Excel yet), I'm pretty sure it will need something else because I can't see how to pluck out the 'score' (which would be just another cell value in a row/column) based on the chosen age range (A) and numeric value (B).