Working on pulling from a range AND another value

[jbrenner51]

I have 40 ranges (two cells each) with weights.

    - 20 for female weight ranges

    - 20 for male weight ranges

Range Examples FEMALE1 is "109.9" (and below)

                           FEMALE2 is a RANGE from "110" to "119.9"

                           FEMALE3 is a RANGE from "120" to "127.5" 

                           ...

                           MALE16 is a RANGE from "240.7" to "256.3"

                           Etc. - 40 of these suckers

I need a formula (not a macro) that sees what is input for weight in F2 and what is input for gender in H2 and then selects a percentile based on those two values and puts the resulting percentile in J2.

If the user puts in their weight "119.5" into F2 and puts "F" into H2 to indicate they are Female, I need to look for the weight in one of the 40 ranges and then select which percentile they are in.

I could hardcode the percentile into the formula, but I don't know how to create an IF or SWITCH or IFS statement and also looks at a second value, like F for female and M for male.

Thank you in advance!

John

[#2]

@jbrenner51 

I'd create a lookup list. In this example it is on the same sheet, but you can place it on a different sheet as well.

You can then use the formula

=IF(H2="F","FEMALE"&MATCH(F2,$N$2:$N$22),IF(H2="M","MALE"&MATCH(F2,$O$2:$O$22),""))

[#3]

@Hans Vogelaar - Thank you for the pointer! I'll work on it over the weekend and will reply again with results. Much appreciated!