lookup

I am trying to use gender and age to look up a score on a scale. My class is
co-ed with 6-12 year olds. There are different grading scales for each
gender and age.

Jonny male age 6 ran a mile in 12 minutes.....I want to look up his 12
minute mile on a scale to reward him with a grade. How do I write a formula
to use gender age and a score?

Susie female age 10 ran a mile in 10:33....I need to look up a completely
different scale for 10 year old female.....

HELP!

Perhaps something like:

=IF(A2="MALE",VLOOKUP(A3,$W$1:$X$100,2,FALSE),IF(A2="FEMALE",VLOOKUP(A3,$Y$1:$Z$100,2,FALSE),""))

Where A2 would contain Male or Female
A3 would contain the parameter to lookup
W1:X100 would contain the Male Table
Y1:Z100 would contain the Female Table.

Of course, you would adjust all these references as necessary and change Male/Female to M/F or whatever way you indicate gender.

Let me explain a bit further,
The first four columns (A-D) are the information, the last four columns
(lets say are J-M) are the lookup
I want in column E to show whether the student was awarded the Pres,
National or Health award.
THANKS!




Name Gender age score Gender Age Mile Award
Sue F 6 11:22 Female 6 10:15
Pres Award
M 6 14:01 12:36 National Award
F 6 9:23 13:00 Health Award

Male 6 9:22 Pres Award
11:40 National Award
12:00 Health Award


"Vito" wrote:

>
> Perhaps something like:
>
> =IF(A2="MALE",VLOOKUP(A3,$W$1:$X$100,2,FALSE),IF(A2="FEMALE",VLOOKUP(A3,$Y$1:$Z$100,2,FALSE),""))
>
> Where A2 would contain Male or Female
> A3 would contain the parameter to lookup
> W1:X100 would contain the Male Table
> Y1:Z100 would contain the Female Table.
>
> Of course, you would adjust all these references as necessary and
> change Male/Female to M/F or whatever way you indicate gender.
>
>
> --
> Vito
> ------------------------------------------------------------------------
> Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
> View this thread: http://www.excelforum.com/showthread...hreadid=489900
>
>

Hi Vito-
I am still struggling with this formula....I attempted to concatenate the
values to lookup, however, I am not figuring it out. I'm not sure how to do
a lookup with the the concatenated cell -
Is there just to many things contingent to look up on too many lookup
columns? ie. gender, grade, and score.


"Vito" wrote:

>
> That's a little more complicated...
>
> First I think you will need to insert a column to both the results area
> and the lookup table to concatenate the values to look up.
>
> Example, if use Column E to string the Gender, Age, and Score (like
> =B1&C1&D1)
>
> Then to the left of the lookup table, in column I, for example, enter
> =J1+K1+L1.
>
> You can always hide these columns.
>
>
> Then your vlookup formula in column F would be
>
> =If(isna(Vlookup(E1,$I$1:$M$100,5,0)),"",Vlookup(E1,$I$1:$M$100,5,0))
>
> Again, you would have to adjust the table range to suit and then copy
> the formula down the list.
>
>
> --
> Vito
> ------------------------------------------------------------------------
> Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
> View this thread: http://www.excelforum.com/showthread...hreadid=489900
>
>

What exactly is happening?

I assumed your lookup table is in columns J, K, L, M with Gender, Age, Scores and Prize, respectively listed and that column titles exist in Row 1.

Note: If your values and tables begin in row 2, then you could copy the formulas below straight to the destination cells and copy them down the sheet. If your values begin in another row, just replace the 2's in the formulas with that row number before copying down. You will also have to adjust the 100's in the formulas to suit the size of your table, equal to the number of rows used.

So, on that assumption, then in the column immediately previous to the table, cell I2, enter =J2+K2+L2 and copy it down the full length of the table.

I then assumed your inputs (Gender, Age, Score) are in columns B,C and D, respectively, with the students name in column A

Now, Instead of concatenating the cells in your input table, try the last formula I posted, so in cell D2 enter =If(isna(Vlookup(B2&C2&D2,$I$2:$M$100,5,0)),"",Vlookup(B2&C2&D2,$I$2:$M$100,5,0))

Hope this helps. Post back if you still have problems and try to say exactly what is happening.

Based on Gender Age and Score, what award would be assigned each student.




Input info Grading scales

Name Gender age score Award Gender Age Time Award
Sue F 6 10:10 ? Female 6 6:00
Pres
Bob M 7 5:02 ? Female 6 10:00
National
Joe F 7 12:24 ? Female 6 15:00
Health
Kelli F 7 5:46 ? Female 7 7:00 Pres
Female 7 11:00 National
Female 7
16:00 Health
Male 6
5:00 Pres
Male 6
9:00 National
Male 6
14:00 Health
Male 7
6:00 Pres
Male 7 10:00 National Male
7 15:00 Health


"fasthands" wrote:

> I am trying to use gender and age to look up a score on a scale. My class is
> co-ed with 6-12 year olds. There are different grading scales for each
> gender and age.
>
> Jonny male age 6 ran a mile in 12 minutes.....I want to look up his 12
> minute mile on a scale to reward him with a grade. How do I write a formula
> to use gender age and a score?
>
> Susie female age 10 ran a mile in 10:33....I need to look up a completely
> different scale for 10 year old female.....
>
> HELP!

Does the formula return any results at all, or are you just having trouble compiling them?

In the lookup table, are the Female and Male awards listed separate (ie. first all Male lookups, then perhaps underneath all Female lookups, or are they intermingled).

Are the scores in the table, just a bottom range and so the actual scores can be anything between the scales in the lookup or the actual scores are available exactly as they are in the lookup table?

These things are necessary to know in order to understand what you need.


If you want me to take a look at the sheet send me your e-mail in a private message and I will send you mine back.