# 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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### 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
2
Views
495
Replies
3
Views
819
Replies
1
Views
2K
Replies
0
Views
2K
Replies
3
Views
445

1,171,627
Messages
5,876,522
Members
433,199
Latest member
guerin47

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back