[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 256

# Returning specific value based on mulitple 'ranges' and variables?

Hi,

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).

Any ideas?!?!

0
Mark_R
1 Solution

Commented:
Sounds vaguely as though you need a lookup table with A ranges down the first column and B ranges across the top and then use INDEX and MATCH. I confess I'm probably only about 30% sure I understood what you were talking about though. A sample workbook with some examples might help a lot... :)
0

Author Commented:
Funnily enough, I saw a thread on =INDEX and =MATCH before posting this but I'm not clued up enough on those (or the LOOKUP functions, to be fair) to know straight off if or how they'd work here

I'll see if I can mess with the data tonight.....thanks for the input on how to arrange it though, I'd not been too sure at all how best to do that.  The format is pretty raw at the moment, so before I post anything I'll tidy it up (I'll also need permission to release it).
0

Commented:
If Rory's right then see this tutorial (example 2)

regards, barry
0

Author Commented:
One more thing, whilst I think about it..........the biggest issue I see is how to pick out the B value from the right range, e.g. a value of 1017 might be entered for someone in the 30-39 age range.  This would give it a 'score' of 2, based on the 100 divisions (n the example (1000-1009 gets 1, 1010-1019 gets 2, etc).
However, the range division is defined within the cell as just as text at the moment (i.e. written as '1010-1019').  How can '1017' be deemed to fall into that range by the formula and then the correctly score assinged?  A format change seems imminent, but to what?
0

Author Commented:
Thanks Barry, I'll check that out.
0

Commented:
Hello Mark, to answer that last point........If the number needs to match within a range then you list the lower bound of each range (in ascending order). For example if you have these numbers in B1:F1

1000, 1010, 1020, 1030, 1040

and your lookup value 1017 is in H1 then this formula

=MATCH(H1,B1:F1,1)

will return 2 (because 1017 matches with the greatest number less than or equal to 1017 in the lookup range, in this case 1010, that's in the second cell of B1:F1 so 2 is returned).

Note the 1 as the 3rd argument of MATCH function, changed from zero in the example I linked to. Zero gives an exact match only.

You can use either type of MATCH function within the INDEX/MATCH/MATCH setup

regards, barry
0

Commented:
@barry - I dont think that will work without converting the text ranges to numbers, I tried it with the text range as descibed and it fails on each boundary, but I usually wind up wrong when I question one of your answers so I may have missed something :)

@mark
If you want to convert the textual range '1010-1019' (in \$B8) to numbers you can use:-
=VALUE(MID(\$B8,1,FIND("-",\$B8)-1)) to get the lower number and/or
=VALUE(RIGHT(\$B8,FIND("-",\$B8)-1)) to get the higher number
This will give you the range you need for the MATCH function in one column as (I think) Barry meant

0

Commented:
Hello regmigrant,

Yes, I was suggesting that only numbers should be used, i.e. the lower boundary of each range - that would make the formulas simpler. If the range is required to be shown that could be in another column.

Mark,

Does it work like this?

An age range is selected (from dropdown) in A2 and then that age-range will have a numeric range associated with it, e.g. 1000-2000 so in B2 a number between those is selected.

You then assign a score (1 to 100) based on where that number falls in the range.

If the range is equally divided into 100 segments you could do it as per the sheet1 method attached

column A has a dropdown which only allows you to pick an age range shown in F2:F8. Column B also has validation so that you can't enter a value that isn't within the relevant range, then C2 uses this formula to get the score

=IF(COUNTA(A2,B2)=2,MIN(INT((B2-VLOOKUP(A2,F\$2:H\$8,2,0))*100/(VLOOKUP(A2,F\$2:H\$8,3,0)-VLOOKUP(A2,F\$2:H\$8,2,0)))+1,100),"")

I cheated a little because with a range like 1000-2000 you score 1 in the range 1000-1009, 2 in 1010-1019 etc....but that would make 2000 score 101 so I had to restrict that with MIN (the last range, therefore, for 100 score, would be 1990-2000 inclusive. Not sure if that's acceptable?

If the divisions within the range aren't 100 equal parts then I think you need to set up a table - that would need to be a table as wide as the number of age ranges......and then 100 rows showing the lower bound for each score (see sheet 2 method - of course you could reverse it to have the age ranges in a column)

For that one I set up validation in a similar way and used this formula in C2

=IF(COUNTA(A2,B2)=2,MATCH(B2,INDEX(G\$3:M\$102,0,MATCH(A2,G\$1:M\$1,0)),1),"")

In that version the ranges can be anything you want them to be, equal or unequal

Maybe neither setup gives you exactly what you need but one of them, at least, might be a good start point

regards, barry
27416410.xls
0

Author Commented:
Wow!  Great responses......I'm humbled, haha!!  :)  Thanks everyone.....

Barry - I messed around a little with that file, adding in the data I'd been given.  I think it is doing what is required, but I'm just waiting for some feedback from the end user to confirm.  I'd only been given a very quick brief, so there may some other things (but the bulk of what is needed seems to be there IMO).
0

Author Commented:
Barry, that turned out to be perfect!  Thanks very much for the help.

Thanks to everyone else who contributed too, I think this thread will be viewed many times in the future! :)
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.