Formula to lookup data in a Vertical format and place in a table

[JPHenning]

Hi,
Could any help me, I am having problems with a workbook, I have a RAW DATA work sheet that has data of electricity consumption for a given week but it is in a vrtical table.

I have many other work work sheets in the workbook that I require to look at the RAW data and the return the correct information in the specified cells

I need the store number that is in cell F1 of each sheet and the Date on each sheet that are on Row4 of each sheet to Look up and match the information in ROW1 for the store number and columnA for the dates.

then in columnB of RAW DATA I have time intervals of 30mins which need to match up with the time intervals on the sheets and display the readings from the RAW data on the sheets.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[JPHenning]

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[JPHenning]

I have attached images of my sheets RAW DATA at the top and the one of the sheets this is not the whole sheet but part.

Please help

 

[JPHenning]

I have come up with this formular but only get #VALUE returned is this anywhere near what I need?

=MATCH((F1,'RAW DATA'!C1:O1)&('089'!B4:E4,'RAW DATA'!A2:A337)&('089'!A6,'RAW DATA'!B2:B337),'RAW DATA'!A1:O337,TRUE)

Please Help.

 

[sanrv1f]

Insert a new column at the begining and Add Redaing Date and Time in that column then use the below formula

VLOOKUP(B$4+$A6,RAW_Data!$A$2:$P$8,MATCH(Sheet2!$F$1,RAW_Data!$A$1:$P$1,0),0)


or

use the below array formula which does not require and helping column

INDEX(RAW_Data!$A$2:$O$8,MATCH(Sheet2!B$4+Sheet2!$A6,RAW_Data!$A$2:$A$8+RAW_Data!$B$2:$B$8,0),MATCH(Sheet2!$F$1,RAW_Data!$A$1:$O$1,0))


fill the formula in the rows below and column E and so on

 

[JPHenning]

Sankar,

I can not seam to get this to work I have tried to admend to get it to work but still get #Value in my cell

any Ideas?

=INDEX('RAW DATA'!$A$2:$O$8,MATCH(Sheet2!B$4+Sheet2!$A6,'RAW DATA'!$A$2:$A$8+'RAW DATA'!$B$2:$B$8,0),MATCH(Sheet2!$F$1,'RAW DATA'!$A$1:$O$1,0))

 

[sanrv1f]

It's an array formula, did you commit with Ctrl+Shift+Enter??

 

[JPHenning]

It's an array formula, did you commit with Ctrl+Shift+Enter??

No I never Now I am getting #N/A

 

[sanrv1f]

a small correction

=INDEX('RAW DATA'!$A$2:$O$8,MATCH(B$4+$A6,'RAW DATA'!$A$2:$A$8+'RAW DATA'!$B$2:$B$8,0),MATCH($F$1,'RAW DATA'!$A$1:$O$1,0))

 

[JPHenning]

a small correction

=INDEX('RAW DATA'!$A$2:$O$8,MATCH(B$4+$A6,'RAW DATA'!$A$2:$A$8+'RAW DATA'!$B$2:$B$8,0),MATCH($F$1,'RAW DATA'!$A$1:$O$1,0))

Still getting #N/A. In purple

Is there any way I could send you the workbook so you could see what I mean??