Cell value lookup based on table range

[sv29]

Hello, was hoping someone could help me with an excel formula, please see attached file. Essentially I want a formula in the yellow highlighted column, which looks up the value (row 3) from the Milestone Range Table based on the % complete (column C) and Group code (column D)

Example;
- Ned is part of group code A and 56% complete, based on these parameters the milestone would be 05
- Joe is part of group code B and 77% complete, based on these parameters the milestone would be 04
- Bill is part of group code E and 10% complete, based on these parameters the milestone would be 01

 

[p45cal]

Cell E5:

=LOOKUP(C5,INDEX($I$5:$R$10,MATCH(D5,$H$5:$H$10,0),),$I$3:$R$3)

copy down.
This won't give you quite your desired results. I'm not sure how robust assigning a blank cell to a milestone is. In Ned's case, his milestone of 4 (50%) would occur for a infinitesimally narrow value of 50% exactly; any less and he's be on milestone 3, any more and he'd be on milestone 5. What milestone would you assign him when he reached 25%? Milestone 2 or 3?

 

[caabyyc]

OR, E5=LOOKUP(C5,OFFSET($H$4,MATCH(D5,$H$5:$H$10,),,1,99),$H$3:$R$3)

 

[sv29]

Cell E5:

=LOOKUP(C5,INDEX($I$5:$R$10,MATCH(D5,$H$5:$H$10,0),),$I$3:$R$3)

copy down.
This won't give you quite your desired results. I'm not sure how robust assigning a blank cell to a milestone is. In Ned's case, his milestone of 4 (50%) would occur for a infinitesimally narrow value of 50% exactly; any less and he's be on milestone 3, any more and he'd be on milestone 5. What milestone would you assign him when he reached 25%? Milestone 2 or 3?

Thank you P45CAL, i was able to make this formula work.