Thread: Lookup Formula for multiple cells with multiple results

If E16 is Male and H16 is over 18 but under 30 then insert whatever number is in R6
However if E16 is Male and H16 is over 30 but under 40 then insert whatever number is in R11
However is E16 is Female and H16 is over 18 but under 30 then insert whatever number is in R31
However is E16 is Female and H16 is over 30 but under 40 then insert whatever number is in R36
There’s about 6 other variations but if you could please write out the entire code covering all 4 variations above I can take it from there. THANK U!!!

Maybe this series of IF statements:

=IF(AND(E16="Male",H16>18,H16<=30),R6,
IF(AND(E16="Male",H16>30,H16<40),R11,
IF(AND(E16="Female",H16>18,H16<=30),R31,
IF(AND(E16="Female",H16>30,H16<40),R36,
"Something else"))))

Just as a heads-up, I added an equal sign before 30 so that it is caught in one of the categories.

Hope this helps.

abousetta

Hi CKD777

I think you should look at the Vlookup function with a TRUE for the last argument. See
http://www.excelfunctions.net/ExcelVlookup.html

I noticed some mistakes in the formula I posted. I have corrected them. Just thought I would let you know to discard the earlier formula.

abousetta

Originally Posted by abousetta

Maybe this series of IF statements:

=IF(AND(E16="Male",H16>18,H16<=30),R6,
IF(AND(E16="Male",H16>30,H16<40),R11,
IF(AND(E16="Female",H16>18,H16<=30),R31,
IF(AND(E16="Female",H16>30,H16<40),R36,
"Something else"))))

Just as a heads-up, I added an equal sign before 30 so that it is caught in one of the categories.

Hope this helps.

abousetta

Thanx for the effort. Still didn't work, but I appreciate you trying to help

If you attach a sample workbook it is much easier to solve this problem.

Click on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area to attach a file.