# Hello gang! I've got two question on how to 'find' some values in a specific data file, namely: - the '52-Week High' of the share price (highest valu

#### FlorisL

##### New Member
Hello gang!

I've got two question on how to 'find' some values in a specific data file, namely:
- the '52-Week High' of the share price (highest value of the share in that specific year)
- 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 looks as follows:

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

Sheet 1:
ABCDE
1
 =VLOOKUP(A2,L2:N44900,3,FALSE)(company '866810' in year 2001)

<tbody>
</tbody>
1/1/2001128668102001
2
 8668102001

<tbody>
</tbody>
1/2/2001118668102001
3
 8668102001

<tbody>
</tbody>
1/3/2001138668102001
4(etc-> up to: '8668102006')(up to last trading day of 2006 -> isn't always 12/31/2006)108668102006
51914162001 company '194162' in year 2001)1/1/20011151914162001
6
 1941622001

<tbody>
</tbody>
1/2/20011001914162001
7
 1941622001

<tbody>
</tbody>
1/3/20011081914162001
8(etc-> up to: '1941622006')(up to last trading day of 2006)1101914162006
98645252001 (company 866810 in year 2001)1/1/200158645252001
1086452520011/2/200178645252001
1186452520011/3/200158645252001
12(etc-> up to: '8645252006')(up to last trading day of 2006)68645252006
135454102001 (company 866810 in year 2001)1/1/2001555454102001
14(etc-> up to: '5454102006')(up to last trading day of 2006)225454102006

<tbody>
</tbody>

My final data set has to look something like this:
-In column 'A' the unique edentifiers (company CUSIP + year)
-In column 'B' the specific year
-In column 'C' the '52-Week High' of the share price (highest value of the share in that specific year) -> red numbers from sheet 1
-In column 'D' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..) -> green numbers from sheet 1
-> off course all the empty cells in column C and D normally contain the missing values

Sheet 2:
 A B C D 1 8668102001 2001 13 2 8668102002 2002 3 8668102003 2003 4 8668102004 2004 5 8668102005 2005 6 8668102006 2006 10 7 1941622001 2001 115 8 1941622002 2002 9 1941622003 2003 10 1941622004 2004 11 1941622005 2005 12 1941622006 2006 110 13 8645252001 2001 7 14 8645252002 (etc..) 2002 (etc..)

<tbody>
</tbody>

The big question is how do I manage to identify the red and green colored values (and link them to 'sheet 2')?

Regarding to the '52-Week High' (red colored in the sheets) 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' this would become '12'
- 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 1: this would be '115'

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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### FlorisL

##### New Member
PS. I'm sorry for the lousy layout.. it is my first post and I didn't know the table wouldn't show the inside borders, as well as the abundance of enters in the post...

Replies
3
Views
215
Replies
0
Views
706
Replies
3
Views
321
Replies
1
Views
208
Replies
1
Views
197