Hi all,

I have some temperature data in a spreadsheet. It's organized like this:



Col A Col B Col C
Date Time Temp

Day X 1 am XX
Day X 2 am XX
Day Y 1 am XX



I realized that this arrangement made it difficult to create a graph of the data for analysis. The graph would treat each row of data as one point on the horizontal axis (the temp is on the vertical axis). What I wanted instead was the horizontal axis to be time (e.g. 2 am, 3 am) ignoring the date, and for the points in the graph to be labeled with the date.

So I thought that a better arrangement of the temp data would be:

Col A Col B (12 am) Col B (2 am) .... Col Y (11 pm)
Date Temp Temp ... Temp



In this way, I can analyze the data meaningfully with 2 graphs

1) A graph showing the temperatures over the course of a particular date.

2) A graph showing the temperatures at a particular time (e.g. 1 am) on different dates.


My question is, how can I populate the data automatically in the original sheet into another sheet with the new arrangement?

I tried using the LOOKUP function (LOOKUP(Q1, Sheet1!B:B,Sheet1!C:C)), but I ran into a wall because I only want Excel to look into the cells of a row which match the date.

So for instance, in the row of the new sheet for Day X, under 1 AM, I would want Excel to lookup the time column in the old sheet for 1 AM, then populate the cell with the temp data on the cell next to 1 AM in the old sheet. Same goes for 2 am, 3 am... But I do not know how to limit this search to just cells in the old sheet matching Day X in the Date column.

Thanks for reading. Hopefully this is something which can be simply solved.

Of course, if there is a better way to go about doing this, I am all ears.

Cheers,
Jay