Bob,

Outstanding advice.
I never knew VLOOKUP could integrate table data into the formula.

Kudos++

Cheers
Bruce Gray



You could use

=VLOOKUP(B4,A31:E36,2+(B3="female")*2)*B6+VLOOKUP( B4,A31:E36,3+(B3="female")
*2)

or if you must dispense with the external table, use

=VLOOKUP(B4,{0,59.512,58.317;3,22.706,20.315;10,17 .686,13.384;18,15.057,14.8
18;30,11.472,8.126;60,11.711,9.082},2+(B3="female" ))*B6
+VLOOKUP(B4,{0,-30.4,-31.1;3,504.3,485.9;10,658.2,692.6;18,692.2,486.6;3 0,87
3.1,845.6;60,587.7,658.5},2+(B3="female"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bruce" wrote in message
...
Bob, this is about as dumbed down as I can get it, and it works fine.
Would have been nice to not rely on an external VLOOKUP table.
If you can see anyway around it, I am all ears....Otherwise, I'll move
onto
something else. :)

Thanks again.
Bruce



Raw Data
Gender B3
Age B4
Weight B6


=IF(B3="male",VLOOKUP(B4,A31:E36,2)*B6+VLOOKUP(B4, A31:E36,3),VLOOKUP(B4,A31:
E36,4)*B6+VLOOKUP(B4,A31:E36,5))


Age Males Females
0 59.512 -30.4 58.317 -31.1
3 22.706 504.3 20.315 485.9
10 17.686 658.2 13.384 692.6
18 15.057 692.2 14.818 486.6
30 11.472 873.1 8.126 845.6
60 11.711 587.7 9.082 658.5