Some kind of vlookup required?
 

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


--
tcpeterso
------------------------------------------------------------------------
tcpeterso's Profile: http://www.excelforum.com/member.php...o&userid=34551
View this thread: http://www.excelforum.com/showthread...hreadid=543174

Some kind of vlookup required?
 

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

"tcpeterso" wrote
in message ...

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


--
tcpeterso
------------------------------------------------------------------------
tcpeterso's Profile:
http://www.excelforum.com/member.php...o&userid=34551
View this thread: http://www.excelforum.com/showthread...hreadid=543174

Some kind of vlookup required?
 

try this, a bit ugly but works..


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=543174

Some kind of vlookup required?
 

Thanks for your reply.

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

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

Thanks for your help.


--
tcpeterso
------------------------------------------------------------------------
tcpeterso's Profile: http://www.excelforum.com/member.php...o&userid=34551
View this thread: http://www.excelforum.com/showthread...hreadid=543174

Some kind of vlookup required?
 

I got it to work. Thanks for your help!


--
tcpeterso
------------------------------------------------------------------------
tcpeterso's Profile: http://www.excelforum.com/member.php...o&userid=34551
View this thread: http://www.excelforum.com/showthread...hreadid=543174