In the following example, the time "13:15" generates the points "65". I want
it to generate the points "60". The formula seems to be connecting the
lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
because the time 13:15 is not fast enough to belong to the "13:00" group, but
fits within the range of "13:01 to 13:45".
How can I change the formula so I get the result of "60" points?

My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
"S2"=13:15 (formatted as [mm]:ss)
'M4'!=the worksheet with the following relevant columns:
Col'G' Col'H'
Run Points
09:20 100
09:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:00 55
14:15 50
14:30 45

Sort your data descending (on column G) and use this formula:

=INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| In the following example, the time "13:15" generates the points "65". I want
| it to generate the points "60". The formula seems to be connecting the
| lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| because the time 13:15 is not fast enough to belong to the "13:00" group, but
| fits within the range of "13:01 to 13:45".
| How can I change the formula so I get the result of "60" points?
|
| My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| "S2"=13:15 (formatted as [mm]:ss)
| 'M4'!=the worksheet with the following relevant columns:
| Col'G' Col'H'
| Run Points
| 09:20 100
| 09:45 95
| 10:00 90
| 10:30 85
| 11:00 80
| 11:15 75
| 12:00 70
| 13:00 65
| 13:45 60
| 14:00 55
| 14:15 50
| 14:30 45
|

That worked, thanks! Here's the next question...(the $100million question)...
That formula worked for an individual case--now I want to globalize it.
I want the PROGRAM to determine the appropriate worksheet from which to
generate the result (so I don't have to enter the worksheet names for each
age category myself).
In the case below, 'M4' referenced the worksheet that has the run scores for
MALES in age category 4 (30-34 years).
In the data that follows, M4 comes from merging the values in columns "M/F"
(Male/Female--which is a manual input, "M") and "Age Cat." [Age
Category--which is derived from the formula
=LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
"4"], having already computed the person's age (difference betw. current date
and their birthdate).
M/F Age Age Cat. CAT.
M 31 4 M4
Thanks for the help!
--NavyPianoMan


"Niek Otten" wrote:

Sort your data descending (on column G) and use this formula:

=INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| In the following example, the time "13:15" generates the points "65". I want
| it to generate the points "60". The formula seems to be connecting the
| lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| because the time 13:15 is not fast enough to belong to the "13:00" group, but
| fits within the range of "13:01 to 13:45".
| How can I change the formula so I get the result of "60" points?
|
| My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| "S2"=13:15 (formatted as [mm]:ss)
| 'M4'!=the worksheet with the following relevant columns:
| Col'G' Col'H'
| Run Points
| 09:20 100
| 09:45 95
| 10:00 90
| 10:30 85
| 11:00 80
| 11:15 75
| 12:00 70
| 13:00 65
| 13:45 60
| 14:00 55
| 14:15 50
| 14:30 45
|

With the category in S3:

=INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| That worked, thanks! Here's the next question...(the $100million question)...
| That formula worked for an individual case--now I want to globalize it.
| I want the PROGRAM to determine the appropriate worksheet from which to
| generate the result (so I don't have to enter the worksheet names for each
| age category myself).
| In the case below, 'M4' referenced the worksheet that has the run scores for
| MALES in age category 4 (30-34 years).
| In the data that follows, M4 comes from merging the values in columns "M/F"
| (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| Category--which is derived from the formula
| =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| "4"], having already computed the person's age (difference betw. current date
| and their birthdate).
| M/F Age Age Cat. CAT.
| M 31 4 M4
| Thanks for the help!
| --NavyPianoMan
|
|
| "Niek Otten" wrote:
|
| Sort your data descending (on column G) and use this formula:
|
| =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "NavyPianoMan" wrote in message
| ...
| | In the following example, the time "13:15" generates the points "65". I want
| | it to generate the points "60". The formula seems to be connecting the
| | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | fits within the range of "13:01 to 13:45".
| | How can I change the formula so I get the result of "60" points?
| |
| | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | "S2"=13:15 (formatted as [mm]:ss)
| | 'M4'!=the worksheet with the following relevant columns:
| | Col'G' Col'H'
| | Run Points
| | 09:20 100
| | 09:45 95
| | 10:00 90
| | 10:30 85
| | 11:00 80
| | 11:15 75
| | 12:00 70
| | 13:00 65
| | 13:45 60
| | 14:00 55
| | 14:15 50
| | 14:30 45
| |
|
|
|

Great! That worked for the run times (sorted descending--meaning
better=faster=higher points). I tried to copy the formula to the pushups
column, but it didn't work there. Maybe it's because that column is sorted
differently (ascending--meaning better=more pushups=higher points)??
Here's an example from my document:
The applicable worksheet for this example is 'M4'--still found in cell I2.
The lookup-value is in cell O2=70. This value should yield 85
points--because it's higher than 69 but not quite 74.
Col'D' Col'E'
Push-Ups Points
31 45
35 50
38 55
41 60
48 65
57 70
64 75
67 80
69 85
74 90
78 95
80 100


"Niek Otten" wrote:

With the category in S3:

=INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| That worked, thanks! Here's the next question...(the $100million question)...
| That formula worked for an individual case--now I want to globalize it.
| I want the PROGRAM to determine the appropriate worksheet from which to
| generate the result (so I don't have to enter the worksheet names for each
| age category myself).
| In the case below, 'M4' referenced the worksheet that has the run scores for
| MALES in age category 4 (30-34 years).
| In the data that follows, M4 comes from merging the values in columns "M/F"
| (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| Category--which is derived from the formula
| =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| "4"], having already computed the person's age (difference betw. current date
| and their birthdate).
| M/F Age Age Cat. CAT.
| M 31 4 M4
| Thanks for the help!
| --NavyPianoMan
|
|
| "Niek Otten" wrote:
|
| Sort your data descending (on column G) and use this formula:
|
| =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "NavyPianoMan" wrote in message
| ...
| | In the following example, the time "13:15" generates the points "65". I want
| | it to generate the points "60". The formula seems to be connecting the
| | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | fits within the range of "13:01 to 13:45".
| | How can I change the formula so I get the result of "60" points?
| |
| | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | "S2"=13:15 (formatted as [mm]:ss)
| | 'M4'!=the worksheet with the following relevant columns:
| | Col'G' Col'H'
| | Run Points
| | 09:20 100
| | 09:45 95
| | 10:00 90
| | 10:30 85
| | 11:00 80
| | 11:15 75
| | 12:00 70
| | 13:00 65
| | 13:45 60
| | 14:00 55
| | 14:15 50
| | 14:30 45
| |
|
|
|

Here you could use a "normal" VLOOKUP formula, like


=VLOOKUP(O2,'M4'!D2:E13,2)

Or of course a variation with the INDIRECT() function

Your data should be sorted ascending

You could use the INDEX/MATCH formula too; just change the -1 to 1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"NavyPianoMan" wrote in message
...
| Great! That worked for the run times (sorted descending--meaning
| better=faster=higher points). I tried to copy the formula to the pushups
| column, but it didn't work there. Maybe it's because that column is sorted
| differently (ascending--meaning better=more pushups=higher points)??
| Here's an example from my document:
| The applicable worksheet for this example is 'M4'--still found in cell I2.
| The lookup-value is in cell O2=70. This value should yield 85
| points--because it's higher than 69 but not quite 74.
| Col'D' Col'E'
| Push-Ups Points
| 31 45
| 35 50
| 38 55
| 41 60
| 48 65
| 57 70
| 64 75
| 67 80
| 69 85
| 74 90
| 78 95
| 80 100
|
|
| "Niek Otten" wrote:
|
| With the category in S3:
|
| =INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "NavyPianoMan" wrote in message
| ...
| | That worked, thanks! Here's the next question...(the $100million question)...
| | That formula worked for an individual case--now I want to globalize it.
| | I want the PROGRAM to determine the appropriate worksheet from which to
| | generate the result (so I don't have to enter the worksheet names for each
| | age category myself).
| | In the case below, 'M4' referenced the worksheet that has the run scores for
| | MALES in age category 4 (30-34 years).
| | In the data that follows, M4 comes from merging the values in columns "M/F"
| | (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| | Category--which is derived from the formula
| | =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| | "4"], having already computed the person's age (difference betw. current date
| | and their birthdate).
| | M/F Age Age Cat. CAT.
| | M 31 4 M4
| | Thanks for the help!
| | --NavyPianoMan
| |
| |
| | "Niek Otten" wrote:
| |
| | Sort your data descending (on column G) and use this formula:
| |
| | =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "NavyPianoMan" wrote in message
| | ...
| | | In the following example, the time "13:15" generates the points "65". I want
| | | it to generate the points "60". The formula seems to be connecting the
| | | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | | fits within the range of "13:01 to 13:45".
| | | How can I change the formula so I get the result of "60" points?
| | |
| | | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | | "S2"=13:15 (formatted as [mm]:ss)
| | | 'M4'!=the worksheet with the following relevant columns:
| | | Col'G' Col'H'
| | | Run Points
| | | 09:20 100
| | | 09:45 95
| | | 10:00 90
| | | 10:30 85
| | | 11:00 80
| | | 11:15 75
| | | 12:00 70
| | | 13:00 65
| | | 13:45 60
| | | 14:00 55
| | | 14:15 50
| | | 14:30 45
| | |
| |
| |
| |
|
|
|

Nevermind my next inquiry--I figured it out. The match_type is +1 instead of
-1.
Thanks for all your help!
--NavyPianoMan

"Niek Otten" wrote:

With the category in S3:

=INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| That worked, thanks! Here's the next question...(the $100million question)...
| That formula worked for an individual case--now I want to globalize it.
| I want the PROGRAM to determine the appropriate worksheet from which to
| generate the result (so I don't have to enter the worksheet names for each
| age category myself).
| In the case below, 'M4' referenced the worksheet that has the run scores for
| MALES in age category 4 (30-34 years).
| In the data that follows, M4 comes from merging the values in columns "M/F"
| (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| Category--which is derived from the formula
| =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| "4"], having already computed the person's age (difference betw. current date
| and their birthdate).
| M/F Age Age Cat. CAT.
| M 31 4 M4
| Thanks for the help!
| --NavyPianoMan
|
|
| "Niek Otten" wrote:
|
| Sort your data descending (on column G) and use this formula:
|
| =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "NavyPianoMan" wrote in message
| ...
| | In the following example, the time "13:15" generates the points "65". I want
| | it to generate the points "60". The formula seems to be connecting the
| | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | fits within the range of "13:01 to 13:45".
| | How can I change the formula so I get the result of "60" points?
| |
| | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | "S2"=13:15 (formatted as [mm]:ss)
| | 'M4'!=the worksheet with the following relevant columns:
| | Col'G' Col'H'
| | Run Points
| | 09:20 100
| | 09:45 95
| | 10:00 90
| | 10:30 85
| | 11:00 80
| | 11:15 75
| | 12:00 70
| | 13:00 65
| | 13:45 60
| | 14:00 55
| | 14:15 50
| | 14:30 45
| |
|
|
|

Thanks.
Why won't this "normal" formula work?
=LOOKUP(V2,{45,50,55,60,65,70,75,80,85,90,95,100}, {"Prob","SatMed","SatHigh","GoodLow","GoodMed","Go odHigh","ExLow","ExMed","ExHigh","OutLow","OutMed" ,"OutHigh"})
(V2 is formatted as a "number" with 2 decimal places.)
I just want, for example, a result (cumulative score) of 81.67 (found in
cell V2) to generate an "ExMed" value. Am I losing my mind? Or perhaps I
just don't get it...
--NavyPianoMan

"Niek Otten" wrote:

Here you could use a "normal" VLOOKUP formula, like


=VLOOKUP(O2,'M4'!D2:E13,2)

Or of course a variation with the INDIRECT() function

Your data should be sorted ascending

You could use the INDEX/MATCH formula too; just change the -1 to 1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"NavyPianoMan" wrote in message
...
| Great! That worked for the run times (sorted descending--meaning
| better=faster=higher points). I tried to copy the formula to the pushups
| column, but it didn't work there. Maybe it's because that column is sorted
| differently (ascending--meaning better=more pushups=higher points)??
| Here's an example from my document:
| The applicable worksheet for this example is 'M4'--still found in cell I2.
| The lookup-value is in cell O2=70. This value should yield 85
| points--because it's higher than 69 but not quite 74.
| Col'D' Col'E'
| Push-Ups Points
| 31 45
| 35 50
| 38 55
| 41 60
| 48 65
| 57 70
| 64 75
| 67 80
| 69 85
| 74 90
| 78 95
| 80 100
|
|
| "Niek Otten" wrote:
|
| With the category in S3:
|
| =INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "NavyPianoMan" wrote in message
| ...
| | That worked, thanks! Here's the next question...(the $100million question)...
| | That formula worked for an individual case--now I want to globalize it.
| | I want the PROGRAM to determine the appropriate worksheet from which to
| | generate the result (so I don't have to enter the worksheet names for each
| | age category myself).
| | In the case below, 'M4' referenced the worksheet that has the run scores for
| | MALES in age category 4 (30-34 years).
| | In the data that follows, M4 comes from merging the values in columns "M/F"
| | (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| | Category--which is derived from the formula
| | =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| | "4"], having already computed the person's age (difference betw. current date
| | and their birthdate).
| | M/F Age Age Cat. CAT.
| | M 31 4 M4
| | Thanks for the help!
| | --NavyPianoMan
| |
| |
| | "Niek Otten" wrote:
| |
| | Sort your data descending (on column G) and use this formula:
| |
| | =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "NavyPianoMan" wrote in message
| | ...
| | | In the following example, the time "13:15" generates the points "65". I want
| | | it to generate the points "60". The formula seems to be connecting the
| | | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | | fits within the range of "13:01 to 13:45".
| | | How can I change the formula so I get the result of "60" points?
| | |
| | | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | | "S2"=13:15 (formatted as [mm]:ss)
| | | 'M4'!=the worksheet with the following relevant columns:
| | | Col'G' Col'H'
| | | Run Points
| | | 09:20 100
| | | 09:45 95
| | | 10:00 90
| | | 10:30 85
| | | 11:00 80
| | | 11:15 75
| | | 12:00 70
| | | 13:00 65
| | | 13:45 60
| | | 14:00 55
| | | 14:15 50
| | | 14:30 45
| | |
| |
| |
| |
|
|
|

That works perfectly for me.
What do you mean with "won't work"?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| Thanks.
| Why won't this "normal" formula work?
|
=LOOKUP(V2,{45,50,55,60,65,70,75,80,85,90,95,100}, {"Prob","SatMed","SatHigh","GoodLow","GoodMed","Go odHigh","ExLow","ExMed","ExHigh","OutLow","OutMed" ,"OutHigh"})
| (V2 is formatted as a "number" with 2 decimal places.)
| I just want, for example, a result (cumulative score) of 81.67 (found in
| cell V2) to generate an "ExMed" value. Am I losing my mind? Or perhaps I
| just don't get it...
| --NavyPianoMan
|
| "Niek Otten" wrote:
|
| Here you could use a "normal" VLOOKUP formula, like
|
|
| =VLOOKUP(O2,'M4'!D2:E13,2)
|
| Or of course a variation with the INDIRECT() function
|
| Your data should be sorted ascending
|
| You could use the INDEX/MATCH formula too; just change the -1 to 1
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| "NavyPianoMan" wrote in message
| ...
| | Great! That worked for the run times (sorted descending--meaning
| | better=faster=higher points). I tried to copy the formula to the pushups
| | column, but it didn't work there. Maybe it's because that column is sorted
| | differently (ascending--meaning better=more pushups=higher points)??
| | Here's an example from my document:
| | The applicable worksheet for this example is 'M4'--still found in cell I2.
| | The lookup-value is in cell O2=70. This value should yield 85
| | points--because it's higher than 69 but not quite 74.
| | Col'D' Col'E'
| | Push-Ups Points
| | 31 45
| | 35 50
| | 38 55
| | 41 60
| | 48 65
| | 57 70
| | 64 75
| | 67 80
| | 69 85
| | 74 90
| | 78 95
| | 80 100
| |
| |
| | "Niek Otten" wrote:
| |
| | With the category in S3:
| |
| | =INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "NavyPianoMan" wrote in message
| | ...
| | | That worked, thanks! Here's the next question...(the $100million question)...
| | | That formula worked for an individual case--now I want to globalize it.
| | | I want the PROGRAM to determine the appropriate worksheet from which to
| | | generate the result (so I don't have to enter the worksheet names for each
| | | age category myself).
| | | In the case below, 'M4' referenced the worksheet that has the run scores for
| | | MALES in age category 4 (30-34 years).
| | | In the data that follows, M4 comes from merging the values in columns "M/F"
| | | (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| | | Category--which is derived from the formula
| | | =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| | | "4"], having already computed the person's age (difference betw. current date
| | | and their birthdate).
| | | M/F Age Age Cat. CAT.
| | | M 31 4 M4
| | | Thanks for the help!
| | | --NavyPianoMan
| | |
| | |
| | | "Niek Otten" wrote:
| | |
| | | Sort your data descending (on column G) and use this formula:
| | |
| | | =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
| | |
| | | --
| | | Kind regards,
| | |
| | | Niek Otten
| | | Microsoft MVP - Excel
| | |
| | | "NavyPianoMan" wrote in message
| | | ...
| | | | In the following example, the time "13:15" generates the points "65". I want
| | | | it to generate the points "60". The formula seems to be connecting the
| | | | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | | | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | | | fits within the range of "13:01 to 13:45".
| | | | How can I change the formula so I get the result of "60" points?
| | | |
| | | | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | | | "S2"=13:15 (formatted as [mm]:ss)
| | | | 'M4'!=the worksheet with the following relevant columns:
| | | | Col'G' Col'H'
| | | | Run Points
| | | | 09:20 100
| | | | 09:45 95
| | | | 10:00 90
| | | | 10:30 85
| | | | 11:00 80
| | | | 11:15 75
| | | | 12:00 70
| | | | 13:00 65
| | | | 13:45 60
| | | | 14:00 55
| | | | 14:15 50
| | | | 14:30 45
| | | |
| | |
| | |
| | |
| |
| |
| |
|
|
|

My apologies--it does work. I had copied the formula from a WORD document
where I was using a tiny font which was different from the other cells on my
worksheet and so I didn't see the result.

"NavyPianoMan" wrote:

Thanks.
Why won't this "normal" formula work?
=LOOKUP(V2,{45,50,55,60,65,70,75,80,85,90,95,100}, {"Prob","SatMed","SatHigh","GoodLow","GoodMed","Go odHigh","ExLow","ExMed","ExHigh","OutLow","OutMed" ,"OutHigh"})
(V2 is formatted as a "number" with 2 decimal places.)
I just want, for example, a result (cumulative score) of 81.67 (found in
cell V2) to generate an "ExMed" value. Am I losing my mind? Or perhaps I
just don't get it...
--NavyPianoMan

"Niek Otten" wrote:

Here you could use a "normal" VLOOKUP formula, like


=VLOOKUP(O2,'M4'!D2:E13,2)

Or of course a variation with the INDIRECT() function

Your data should be sorted ascending

You could use the INDEX/MATCH formula too; just change the -1 to 1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"NavyPianoMan" wrote in message
...
| Great! That worked for the run times (sorted descending--meaning
| better=faster=higher points). I tried to copy the formula to the pushups
| column, but it didn't work there. Maybe it's because that column is sorted
| differently (ascending--meaning better=more pushups=higher points)??
| Here's an example from my document:
| The applicable worksheet for this example is 'M4'--still found in cell I2.
| The lookup-value is in cell O2=70. This value should yield 85
| points--because it's higher than 69 but not quite 74.
| Col'D' Col'E'
| Push-Ups Points
| 31 45
| 35 50
| 38 55
| 41 60
| 48 65
| 57 70
| 64 75
| 67 80
| 69 85
| 74 90
| 78 95
| 80 100
|
|
| "Niek Otten" wrote:
|
| With the category in S3:
|
| =INDEX(INDIRECT(S3&"!H2:H13"),MATCH(S2,INDIRECT(S3 &"!G2:G13"),-1))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "NavyPianoMan" wrote in message
| ...
| | That worked, thanks! Here's the next question...(the $100million question)...
| | That formula worked for an individual case--now I want to globalize it.
| | I want the PROGRAM to determine the appropriate worksheet from which to
| | generate the result (so I don't have to enter the worksheet names for each
| | age category myself).
| | In the case below, 'M4' referenced the worksheet that has the run scores for
| | MALES in age category 4 (30-34 years).
| | In the data that follows, M4 comes from merging the values in columns "M/F"
| | (Male/Female--which is a manual input, "M") and "Age Cat." [Age
| | Category--which is derived from the formula
| | =LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
| | "4"], having already computed the person's age (difference betw. current date
| | and their birthdate).
| | M/F Age Age Cat. CAT.
| | M 31 4 M4
| | Thanks for the help!
| | --NavyPianoMan
| |
| |
| | "Niek Otten" wrote:
| |
| | Sort your data descending (on column G) and use this formula:
| |
| | =INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "NavyPianoMan" wrote in message
| | ...
| | | In the following example, the time "13:15" generates the points "65". I want
| | | it to generate the points "60". The formula seems to be connecting the
| | | lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| | | because the time 13:15 is not fast enough to belong to the "13:00" group, but
| | | fits within the range of "13:01 to 13:45".
| | | How can I change the formula so I get the result of "60" points?
| | |
| | | My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| | | "S2"=13:15 (formatted as [mm]:ss)
| | | 'M4'!=the worksheet with the following relevant columns:
| | | Col'G' Col'H'
| | | Run Points
| | | 09:20 100
| | | 09:45 95
| | | 10:00 90
| | | 10:30 85
| | | 11:00 80
| | | 11:15 75
| | | 12:00 70
| | | 13:00 65
| | | 13:45 60
| | | 14:00 55
| | | 14:15 50
| | | 14:30 45
| | |
| |
| |
| |
|
|
|