Need help combining data + vlookup multiple values + Pivot table

[calhawk01]

Hi, I'm working with this data:
Date Time Open High Low Close Volume
1/3/2012 9:30:00 AM 127.77 127.88 127.73 127.84 1332008
1/3/2012 9:31:00 AM 127.84 127.88 127.812 127.84 712398
1/3/2012 9:32:00 AM 127.84 127.90 127.81 127.88 529695

1/4/2012 9:31:00 AM 127.26 127.31 127.24 127.3 554619
1/4/2012 9:32:00 AM 127.3 127.37 127.28 127.31 549311
1/4/2012 9:33:00 AM 127.31 127.33 127.22 127.24 697424

---
What I want to do is get the lowest, highest, the opening and closing price and the time that is associate with them. For the above example I want to find a formula that would return:

1/3/2012 Open= 127.77@9:30AM, High= 127.90@9:32AM, Low= 127.73@9:30AM and Close= 127.88@9:32AM
1/4/2012 Open= 127.26@9:30AM, High= 127.37@9:32AM, Low= 127.22@9:33AM and Close= 127.24@9:33AM
----------

What I started doing was, I created a pivot point for the entire data. I was easily able to use the "min" and "max" function in pivot tables to find the high and low. However I was not able to retrieve the time associated with them. I was thinking maybe I should go back to the data source and "combine" the time cell and the value together? Also, I was not able to retrieve the Opening value and Closing value using pivot table because I don't know how to get the "beginning" and "ending value" for a given value. Because the opening and ending value always shows up in the beginning and end of the data that I have.

Thanks for your help!

[Sadath31]

Hi
Instead of Pivot table I have used formula.
See the attached workbook helps.

Sadath

[Sadath31]

Hi
sorry, attached here again the Excel 2003 ver.

Sadath

[calhawk01]

Genius! Woah, thank you very much. It works! I appreciate your help

[calhawk01]

Hey buddy,

Thanks for your help on my thread. For some reason every time I change your formula to adjust to more cells, I keep getting #N/A or #VALUE.

For example of Open, I changed your formula from:
=INDEX(C2:C15,MATCH(J2&"@"&L2,A2:A15&"@"&B2:B15,0))

To:
=INDEX(C2:C1012878,MATCH(J2&"@"&L2,A2:A1012878&"@"&B2:B1012878,0))

or:
=INDEX(C:C,MATCH(J2&"@"&L2,A:A&"@"&B:B,0))

Do you know what I'm doing wrong?

[calhawk01]

Book3.xlsx
I notice that when I downloaded your file, all of your formulas have "{" at the beginning and "}" at the end. And if i try to edit your formula, the formula does not work. I tried to even type the formula but it doesnt work.
Thanks

[DGagnon]

when you edit the formula the { and } will disapear, you need to confirm the formula with Ctrl+Shift+Enter, this will cause the formula to be treated as an array.

[calhawk01]

Nvm... with the info that you provided in your spreadsheet.. and the help of google, i figured it out and learned a lot more about excel!