Some kind of vlookup required?

[tcpeterso]

My data set looks like the following below. I have data set for "Males"
and one for "Females"

Based on whether male or female is identified, the other (2)
identifying variables are age (within the ranges for each column) and
number of reps completed down the far left column.

By identifing:
1) Male or Female
2) Age
3) Number of Repititions

I need to have it tell me the crossreferenced "score".

For example: If the data table below was for "males" and the age was
43 and the person did 21 repitions, the returned score to me would be
50.

Your help with figuring out this formula is much appreciated!



Reps 17-21 22-26 27-31 32-36 37-41 42-46 47-51
0 0 0 0 0 0 0 0
5 9 20 24 28 30 32 36
6 10 21 25 29 31 33 38
7 12 22 26 30 32 34 39
8 13 23 27 31 33 36 40
9 14 25 28 32 34 37 41
10 16 26 29 33 35 38 42
11 17 27 31 34 36 39 44
12 19 28 32 35 37 40 45
13 20 29 33 36 38 41 46
14 21 30 34 37 39 42 47
15 23 31 35 38 41 43 48
16 24 33 36 39 42 44 49
17 26 34 37 41 43 46 51
18 27 35 38 42 44 47 52
19 28 36 39 43 45 48 53
20 30 37 40 44 46 49 54
21 31 38 41 45 47 50 55
22 32 39 42 46 48 51 56
23 34 41 43 47 49 52 58

 

[Biff]

Hi!

You'd need to change the age labels to be the lower bound for each range:

17-21 22-26 27-31 32-36 37-41 42-46 47-51

Change to: 17;22;27;32;37;42;47

Assume your table is in the range A1:H21

B1:H1 are the age labels:

17;22;27;32;37;42;47

J1 = age = 43
J2 = reps = 21

=VLOOKUP(J2,A1:H21,MATCH(J1,A1:H1,1),0)

Returns 50

An age <17 or reps >23 will return #N/A

Biff

 

[simonsmith]

try this, a bit ugly but works..

 

[tcpeterso]

Thanks for your reply.

I tried the formula that you gave me and it was returning a number tha
is slightly off from what it shold be.

Is there a revision to the formula or different approach that we ca
take with this?

Thanks for your help

 

[tcpeterso]

I got it to work. Thanks for your help!