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(A 2="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

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(A 2="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

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(E 1,$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

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(E 1,$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 i
Row 1.

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

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

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

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

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

--
Vit
-----------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...fo&userid=2918
View this thread: http://www.excelforum.com/showthread.php?threadid=48990

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.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489900