Filling in cells based on value inserted on one previous cell

[boakley]

I am going to try and explain this the best way I can because I cannot seem to figure this out.

I have made two seperate tables on a different sheet, one table for score standards for female and the other table for score standards for males to use with VLOOKUP.

I want cell A1 to be a dropbox with the option to choose either male or female.

In cell A2, I would enter the amount of repititions completed by that male or female.

Once A2 has been entered, I would like to see the score auto filled in cell A3 using VLOOKUP to automatically fill in the score (1-100 points). But it would be conditional on whether male or female was chosen in A1 since the scores are different for male and female.

Essentially, the choosing of either male or female in cell A1 would be the deciding factor on which score table the computer uses to fill in the rest.

Is this possible?

Any help would be amazing!!!

 

[boakley]

I forgot to mention that after the choice of male or female was made in cell A1, cell A2 would actually be the age of that individual and those two things would be the deciding factor for everything else. I am so sorry that this may be some what confusing. But I am stumped. I hope someone can help me out here.

 

[boakley]

.

 

[Cross Terrier]

=if(A$1="Male",vlookup(A$2,MaleSheet!A:B,2,false),if(A$1="Female",vlookup(A$2,FemaleSheet!A:B,2,false),""))

This assumes you have Male/Female in A1 and their age in A2, and will look up the reference on the correct data sheet depending on the content of A1.

 

[boakley]

Awesome! So would I have to put both equations in the cell so that the computer would know which one to look up?
For example:
=if(A$1=“Male”,vlookup(A$2,MaleSheet!A:B,2,false)
and
=if(A$1=“female”,vlookup(A$2,FemaleSheet!A:B,2,false)

 

[boakley]

.