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

#### JPHenning

##### Board Regular
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]

Last edited:

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

I have come up with this formular but only get #VALUE returned is this anywhere near what I need?
Code:
``=MATCH((F1,'RAW DATA'!C1:O1)&('089'!B4:E4,'RAW DATA'!A2:A337)&('089'!A6,'RAW DATA'!B2:B337),'RAW DATA'!A1:O337,TRUE)``

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

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))

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

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

No I never Now I am getting #N/A

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))

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??

Replies
0
Views
486
Replies
0
Views
455
Replies
1
Views
819
L
Replies
7
Views
493
Replies
5
Views
446

1,203,174
Messages
6,053,928
Members
444,694
Latest member
JacquiDaly

### 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