|
VLOOKUP won't generate correct response for "inbetween" values
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 |
VLOOKUP won't generate correct response for "inbetween" values
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 | |
Going to the proper worksheet to get the result
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 | |
Going to the proper worksheet to get the result
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 | | | | | |
Going to the proper worksheet to get the result
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 | | | | | |
Going to the proper worksheet to get the result
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 | | | | | | | | | | | | |
Going to the proper worksheet to get the result
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 | | | | | |
Can't get this LOOKUP formula to work
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 | | | | | | | | | | | | |
Can't get this LOOKUP formula to work
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 | | | | | | | | | | | | | | | | | | | | | | |
Can't get this LOOKUP formula to work
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 | | | | | | | | | | | | |
| All times are GMT +1. The time now is 05:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com