Index ctrl-shift entered formula not giving the results I was hoping for

Good day everybody, again...
Yeah, I'm having another question and it is (surprisingly) about the same database every other question so far I have asked about...
Well, what I am facing now is that I am using one of my entrusted formulae (cell H2, in this case I made it a text-entered value) is not delivering the results I hoped for (the results as shown in column F).
So I am looking for the right (probably ctrl-shift entered) formula that gives these results.

Note that the list with numbers in column C and E is varying all the time. That means that the list in column E and F is ever changing, growing or shifting. So the formula has to be able to cover that without making any mistakes (I want to use this column E for something else, in another tab).

So see attachment and already thanks in advance for thinking along.

Note that the list with numbers in column C and E is varying all the time. That means that the list in column E and F is ever changing, growing or shifting. So the formula has to be able to cover that without making any mistakes

That said I would recommend setting up some dynamic named ranges (DNR) in Name Manager because:
1. they dynamically adjust in size to fit the data
2. reduce the need to edit formulas with new data
3. make formulas much easier to write, follow, edit and trouble-shoot

You will find these in the attached Name Manager. This upload will adjust for regional settings replacing the "," argument separators with ";". (I don't trust my typing skills enough to edit the formulas accurately in this post.)

Even though, for others watching this thread here is the list of DNRs (without regional adjustments):

Note all of these are defined with reference to the size of column A (Code)

Code	=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzz",Sheet1!$A:$A))
Country	=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH("zzzzz",Sheet1!$A:$A))
Index_Match	=Sheet1!$E$2:INDEX(Sheet1!$E:$E,MATCH("zzzzz",Sheet1!$A:$A))
Total	=Sheet1!$C$2:INDEX(Sheet1!$C:$C,MATCH("zzzzz",Sheet1!$A:$A))

Then (CSE) the formula in F2 filled down (I did it in G2 to demonstrate) is

Formula:

Please Login or Register  to view this content.

Also given the shifting of data you mention I recommend this change to your formula in E2 and filled down.

Formula:

Please Login or Register  to view this content.

in the event the data shifts rows and the ROW() function changes the ROWS($E$2:$E2) will maintain without need for editing.

Yeah, that's doing the trick. Thanks!
I'm going not only to implement the formula but also study it. As I always do.
Thanks a lot, I'm happy with it.

@Daniel - are you using Excel 2007 or something much newer? If the latter, please update your profile (for example, 2007 may in your case be a release number for MS365).