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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

try this, a bit ugly but works..

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.

I got it to work. Thanks for your help!

I have same fitness question but I have trouble writing the IF(I2=male, ....etc. How do I write the same thing above but with the start of =IF ....? First time I am here. Thanks so much for your help.

Thank you, grizzly! I thought I read somewhere that if the same post was on the board, you shouldn't make the same question again. I'll try now. Thanks again!