Sumif Lookup

Yeah, you are going to like this one. I used some helper cells. I recommend you use them too.

First you are going to have to determine how many times the name appears in column C =COUNTIF($C$1:C18,C18). (Cell P18)

Then you are going to have to determine where the previous occurrence of the name in column C occurs. =LARGE((($C$1:C18=C18)*ROW($C$1:C18)),COUNTIF($C$1:C18,C18)-P18+2) <- this is an array formula. (Cell Q18)

Then you need to get the actual value in Column I for this value. =INDEX($I:$I,Q18,1) (Cell R18).
If you duplicate this formula in cell V18 and then substitute everything back until you eliminated references to the helper cells so that only original cell references are used in the formula, you get: =INDEX($I:$I,LARGE((($C$1:C18=C18)*ROW($C$1:C18)),COUNTIF($C$1:C18,C18)-COUNTIF($C$1:C18,C18)+2),1).

This is the formula fragment you would have to place in each part of the formula where I17 is referenced. Also entered as an array formula.

This is not yet enough. You will have to apply the COUNTIF formula and if evaluates to 1 then use the original formula, otherwise, use this formula.

I have not done these last two steps, but it leads to one very hairy formula. I suggest you modify the formula in cell V18 (wrap it in an if statement with the COUNTIF) and reference it wherever I17 is referenced. Move this formula off to some convenient column and keep it as a helper cell.

I told you it would be hairy . As best I can tell, it does look correct. It's equal to column I in the same row for the first occurrence and then the complicated formula for the previous value if not.

The best way to QA this would be to edit your formula manually in the old spreadsheet and see if it gives you the same answer as the formula in the new spreadsheet. In my testing, I took it as far as to see that I got the value of I10 when the formula was in row 18.