if value not present, point to next item

[fathead7466]

I am making a BF% chart using excel. I have a height and weight chart, if they exceed it, there is a circuference value to match with person's height. BF% is based on if value is present, then point to value according the tables associated with height and circuference value. Four table are present, two for male, two for female. I am using 2010 at home, 2003 at work where we use it to upload to online prgram that tracks all data inputted.

[code]

=IF(AND(I6>=60,I6<=69.5,H6="M"),INDEX('M 60-69.5'!B4:U25, MATCH((L6-K6),'M 60-69.5'!A4:A25,0),MATCH('FEB 2012 MOC BCA'!I6,'M 60-69.5'!B3:U3,0)),IF(AND(I6>=70,I6<=79.5,H6="M"),INDEX('M 70-79.5'!B4:U28, MATCH((L6-K6),'M 70-79.5'!A4:A28,0),MATCH('FEB 2012 MOC BCA'!I6,'M 70-79.5'!B3:U3,0)),""))

for male on M6

[code]

=IF(AND(I7>=58,I7<=67.5,H7="F"),INDEX('F 58-67.5'!B4:U40, MATCH(L7-K7,'F 58-67.5'!A4:A40,0),MATCH(IFERROR('FEB 2012 MOC BCA'!I7,""),'F 58-67.5'!B3:U3,0)),IF(AND(I7>=68,I7<=77.5,H7="F"),INDEX('F 68-77.5'!B4:U41, MATCH((L7-K7),'F 68-77.5'!A4:A41,0),MATCH('FEB 2012 MOC BCA'!I7,'F 68-77.5'!B3:U3,0)),""))

for female on m7.

It shows #NA. I dont think I'm point to the correct value when there is no value there. I have attached the workbok.



AGE GENDER HT WT NK ABS/WAIST BF%
22 M 69 189 17 35 20
M 69 154
23 M 73 210 16.5 35 15
29 M 72 196
19 M 65.0 199 16 36 22
22 F 63 144 16 38 #N/A

[protonLeah]

In order to wrap your code you need a [/code] tag after each code block: you are missing two of them. Place one after each "))"

Also, there is no workbook attached.

[darknation144]

You still haven't added a workbook