Heres the Formula Im Using And It works to an Extent... The Only Problem Im
having is that its not always an Exact Number for the L7 that its looking for
so it always takes the Lower # but I want it to take the Next Number Higher.
In the Examples provide if it was a 1332 It would take the Value for 1330
instead of the Desired 1336.

Help Please??
Chad

=IF(OR(G7="",G7<17,L7=""),"",VLOOKUP(L7,'Male
Tables'!N4:X140,IF(G761,11,ROUNDUP((G7-16)/5+1,0)),TRUE))


Male Tables Example

Reps 17-21 22-26 27-31 32-36 37-41 42-46
0 100 100 100 100 100 100
1300 100 100 100 100 100 100
1306 99 99 100 100 100 100
1312 97 98 100 100 100 100
1318 96 97 100 100 100 100
1324 94 96 99 99 100 100
1330 93 94 98 98 100 100
1336 92 93 97 97 100 100
1342 90 92 96 96 99 100
1348 89 91 95 95 98 100
1354 88 90 94 95 97 100
1400 86 89 92 94 97 100
1406 85 88 91 93 96 100
1412 83 87 90 92 95 99
1418 82 86 89 91 94 98
1424 81 84 88 90 93 97
1430 79 83 87 89 92 97
1436 78 82 86 88 91 96
1442 77 81 85 87 91 95
1448 75 80 84 86 90 94

Try:

=IF(OR(G7="",G7<17,L7=""),"",INDEX('Male Tables'!N4:X140,MATCH(L7,'Male
Tables'!N4:N140,1),IF(G761,11,ROUNDUP((G7-16)/5+1,0))))

"CERYD" wrote:

Heres the Formula Im Using And It works to an Extent... The Only Problem Im
having is that its not always an Exact Number for the L7 that its looking for
so it always takes the Lower # but I want it to take the Next Number Higher.
In the Examples provide if it was a 1332 It would take the Value for 1330
instead of the Desired 1336.

Help Please??
Chad

=IF(OR(G7="",G7<17,L7=""),"",VLOOKUP(L7,'Male
Tables'!N4:X140,IF(G761,11,ROUNDUP((G7-16)/5+1,0)),TRUE))


Male Tables Example

Reps 17-21 22-26 27-31 32-36 37-41 42-46
0 100 100 100 100 100 100
1300 100 100 100 100 100 100
1306 99 99 100 100 100 100
1312 97 98 100 100 100 100
1318 96 97 100 100 100 100
1324 94 96 99 99 100 100
1330 93 94 98 98 100 100
1336 92 93 97 97 100 100
1342 90 92 96 96 99 100
1348 89 91 95 95 98 100
1354 88 90 94 95 97 100
1400 86 89 92 94 97 100
1406 85 88 91 93 96 100
1412 83 87 90 92 95 99
1418 82 86 89 91 94 98
1424 81 84 88 90 93 97
1430 79 83 87 89 92 97
1436 78 82 86 88 91 96
1442 77 81 85 87 91 95
1448 75 80 84 86 90 94

Tried using the Index but All I am getting is #N/A now.


"Toppers" wrote:

Try:

=IF(OR(G7="",G7<17,L7=""),"",INDEX('Male Tables'!N4:X140,MATCH(L7,'Male
Tables'!N4:N140,1),IF(G761,11,ROUNDUP((G7-16)/5+1,0))))

"CERYD" wrote:

Heres the Formula Im Using And It works to an Extent... The Only Problem Im
having is that its not always an Exact Number for the L7 that its looking for
so it always takes the Lower # but I want it to take the Next Number Higher.
In the Examples provide if it was a 1332 It would take the Value for 1330
instead of the Desired 1336.

Help Please??
Chad

=IF(OR(G7="",G7<17,L7=""),"",VLOOKUP(L7,'Male
Tables'!N4:X140,IF(G761,11,ROUNDUP((G7-16)/5+1,0)),TRUE))


Male Tables Example

Reps 17-21 22-26 27-31 32-36 37-41 42-46
0 100 100 100 100 100 100
1300 100 100 100 100 100 100
1306 99 99 100 100 100 100
1312 97 98 100 100 100 100
1318 96 97 100 100 100 100
1324 94 96 99 99 100 100
1330 93 94 98 98 100 100
1336 92 93 97 97 100 100
1342 90 92 96 96 99 100
1348 89 91 95 95 98 100
1354 88 90 94 95 97 100
1400 86 89 92 94 97 100
1406 85 88 91 93 96 100
1412 83 87 90 92 95 99
1418 82 86 89 91 94 98
1424 81 84 88 90 93 97
1430 79 83 87 89 92 97
1436 78 82 86 88 91 96
1442 77 81 85 87 91 95
1448 75 80 84 86 90 94