I think this will give you what you want:

=IF(OR(D9=$C$23,D9=$C$24,D9=$C$25),VLOOKUP(C9,$B$3 0:$E$34,2,0),IF(OR(D9=$D$23,D9=$D$24,D9=$D$25,D9=$ D$26),VLOOKUP(C9,$B$30:$E$34,3,0),VLOOKUP(C9,$B$30 :$E$34,4,0)))

If D9 is 1, 4 or 5 the residual% is the first column, if D9 is 2, 3, 6
or 10 the residual% is the middle column, otherwise it is the last
column, and in each case the value of C9 determines which row of the
table is used.

Hope this helps.

Pete