Help with a tricky lookup or index/match scenario

Good morning,

I'm stumped. I've tried several varieties of lookups and index/match but can't seem to solve this one. My source data comes in a specific format so I'm trying to make the formulas work based on what I get without having to modify the original data. I'm attaching an oversimplified version of my source data. What I'm getting stumped on:

1) The "Name" is only in the first cell and is blank for the cells below (though it references the same name)
2) the "Products" column isn't consistent. This takes away offset options
(Both in the blue table)

I hope to solve for the blank cells in the green table.

Hard one to explain... I hope this makes sense when you see the table.

Thanks in advance!
Frank

Thanks for the welcome, Ford and thanks for the suggestions. Your solution definitely would make life easier if I could add that column. I should have clarified that the blue table = my source data that I am linking from to a new spreadsheet which is my green table. I'm looking at the blue table as unchangeable, however, I can modify the green table if that helps.

The helper column will get me where I need to go. Thanks for your help and for the welcome!

you can use the following formula

If you like it please heads up for me...thanks

sheep solution.jpg