Extract Values from a Table Cell Using Ranges

Hello everyone,

I'm trying to extract data from a certain cell in a table, but instead of using exact values for one of the table's axes, I only have age and ID ranges.

excel.jpg

In this table, I need to get a value from a cell based on an age range (listed in column B) and an ID range (listed in row 4). So, if a user is 27 and has an identifier of 7, I need to be able to get a result of 2.3 back. Only problem is I can only get this to work if I specify the headings in exact values, i.e. I can only get a result if I say "26-30" and "6-7." The user needs to specify their exact age, not a range. But if they do, my spreadsheet won't be able to find the proper info because, obviously, "27" doesn't equal "26-30." Nor does "7" equal "6-7." Is there a way that I can get my spreadsheet to look at the data in column B and row 4 as ranges instead of exact values?

I've done a Google search on this problem and have found that there is a way to do this using left() and right() formulas, but I can't follow the logic on the pages I read this from. And when I just regurgitate (which isn't the best way to learn) the formulas, I get an error.

Can anyone help?

Whoa.

Not being in Excel for too long, that expression looks crazy. Ok. Uhm...so I guess the substitute() function takes all dashes from the identifier ranges as replaces it with 10 spaces, like you said. So "3-5" becomes "3 5". What do the left() and right() functions do with those substitutions?

Where does H4 come into this? Is that where I put my identifier value?

I broke it down like this:

=SUMPRODUCT( <== this will give me the sum of the products of ranges, which are comma delimited?
	(LEFT(SUBSTITUTE(C4:F4,"-",REPT(" ",10)),3)*1<=H4)*    <== this takes the first three characters of the identifier ranges after the dashes are replaced by spaces (so the first number of the range)
	(RIGHT(SUBSTITUTE(C4:F4,"-",REPT(" ",10)),3)*1>=H4)*    <== this takes the last three characters of the identifier ranges after the dashes are replaced by spaces (last number)
	(LEFT(SUBSTITUTE(B5:B11,"-",REPT(" ",10)),3)*1<=H5)*    <== this takes the first three characters of the age ranges after the dashes are replaced by spaces (so the first number of the age)
	(RIGHT(SUBSTITUTE(B5:B11,"-",REPT(" ",10)),3)*1>=H5)*    <== this takes the last three characters of the age ranges after the dashes are replaced by spaces (last number)
	(C5:F11)  <== this is the table range
)

I guess it's taking everything as one array? I'm a little confused.