That works perfectly for me.
What do you mean with "won't work"?
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| 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","GoodHigh","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
| >
| >
| > | > | 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
| > | >
| > | > | > | > | 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
| > | > | >
| > | > | > | > | > | > | 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
| > | > | > |
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >
