How to identify two specific values in one column

Hi everyone!

I've got two question on how to 'find' some values in a specific data file, namely:
1. the '52-Week High' of the share price (highest value of the share in that specific year)
2. the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

Simplified, my data 'Before' looks as follows: Before.xlsx

-In column 'A' I've a created a line of unique identifiers (company CUSIP + year -> combined column B and C), in my original set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows
-In column 'B' the year is noted
-In column 'C' the company's CUSIP (identifier for specific company)
-In column 'D' the date is shown -> its daily trading data
-In column 'E' name of the company


My final data set 'After' has to look something like this: After.xlsx

-In column 'A' the unique identifiers (company CUSIP + year)
-In column 'B' the year is noted
-In column 'C' the company's CUSIP (identifier for specific company)
-In column 'D' name of the company
-In column 'E' the '52-Week High' of the share price (highest value of the share in that specific year)
-In column 'F' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

The big question is how do I manage to identify an link the following data from the 'Before'-sheet to the 'After'-sheet:
1. '52-Week High' of the share price (highest value of the share in that specific year)
2. value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

Regarding to the '52-Week High' I've already tried with the following functions:
- VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' in the sheet 'Before' this would become '13.04'
- Combined INDEX/MATCH: The same problem as with VLOOKUP
- IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 'Before': this would be '69.86'

Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.

Since I'm already stuck on the same point because of these problems, I really hope someone could help me with these questions !!

Regards,
Floris

Attachments:
- Attachment 303689
- Attachment 303690