Lookup 3 values from different tables

[kikareng]

Hi guys,

I am trying to set up a summary sheet, and collect data from the raw data sheet.

In my spreadsheet, I got a worksheet has all the data from different projects. Each project will have its own small table, with date in row and items in column and the heading of the table is the project names. I created another summary sheet, that I want to combine data base on the items. Therefore, the table will have item as heading, and date in row and project names in column.

For Example:
In Raw Data
Shop1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Feb-17[/TD]
[/TR]
[TR]
[TD]Staff[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
There will be a separate table for shop 2, shop 3 etc.

Summary sheet:
Staff
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Feb-17[/TD]
[/TR]
[TR]
[TD]Shop1[/TD]
[TD](formula)[/TD]
[/TR]
[TR]
[TD]Shop2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The following is the formula I put into the summary sheet:
=VLOOKUP(A9,'Projects'!A:A,VLOOKUP(B$8&$A$7,'Projects'!$D:E,0),FALSE)

A9 is Shop 1, it doesn't work. Please help me with this. Thank you!

 

[Aladin Akyurek]

Is Shop 1 the name of the sheet housing the data related to Shop 1?

 

[kikareng]

Is Shop 1 the name of the sheet housing the data related to Shop 1?

Hi Aladin Akyurek,

Shop1 is the heading of the table. I inserted a table, but when I post it, the table line is gone...
It is just a row above the table of data for Shop1, than after the Shop1 table, there is a Shop2 table after this with exactly the same format. And the sheet contains those table is called "Projects".

And in other worksheet call "Summary", I want the Shop1, Shop2 etc. line up in a column and item name (staff, sales etc.) become the heading (like switch them around) and the date still in the row.

In my formula, I try to lookup the headings from "Projects" sheet (ie Shop1, Shop2 etc.), item names, and Date, then return a number.