How to find two values in one column (sounds so simple..)

Hello everyone!

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:
1.JPG


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:
2.JPG


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

In C1 of Sheet2 you can have this array* formula:

=MAX(IF(Sheet1!B$1:B$1000=A1,Sheet1!D$1:D$1000))

It is not a good idea to use full-column references in array formula, so adjust the ranges to suit your data (but see below *).

NOTE: you might have to use semicolons instead of commas in the formula.

* An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then you will see curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula then you have to use CSE again to commit it. The formula can be copied to another cell(s) in the "usual" ways.

Note also that a picture of a sheet is not of much use - post a sample workbook instead.

Hope this helps.

Pete

Sorry guys for the images.. it's my first post. I'll remove/close this post and open a new thread containing clear example workbooks

Hi Martin,

Tnx for the advice!

I created a new thread (containing clear workbooks) is: http://www.excelforum.com/excel-gene...ml#post3618277