Pull sales data from another workbook, possibly using VLOOKUP

[tlg]

Hi

I have a workbook (salesdata.xls) which contains sales information for a full year by department, it looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Dept A[/TD]
[TD]Dept B[/TD]
[TD]Dept C[/TD]
[TD]Dept D[/TD]
[TD]Dept E[/TD]
[TD]Dept F[/TD]
[TD]Dept G[/TD]
[/TR]
[TR]
[TD]Week 1[/TD]
[TD]700[/TD]
[TD]520[/TD]
[TD]100[/TD]
[TD]620[/TD]
[TD]44[/TD]
[TD]120[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Week 2[/TD]
[TD]125[/TD]
[TD]522[/TD]
[TD]144[/TD]
[TD]800[/TD]
[TD]55[/TD]
[TD]170[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Week 3[/TD]
[TD]122[/TD]
[TD]400[/TD]
[TD]200[/TD]
[TD]120[/TD]
[TD]44[/TD]
[TD]150[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Week 4[/TD]
[TD]100[/TD]
[TD]350[/TD]
[TD]180[/TD]
[TD]100[/TD]
[TD]70[/TD]
[TD]190[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Week 5[/TD]
[TD]230[/TD]
[TD]400[/TD]
[TD]158[/TD]
[TD]164[/TD]
[TD]80[/TD]
[TD]170[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

On a separate workbook (depts.xls) I want to pull information by department for a specific week, like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dept A[/TD]
[TD] [/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week Number:[/TD]
[TD]4[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need the sheet to be able to pull the sales for that department for the week specified.

I know how to use VLOOKUP to pull this information using a formula for a specific row or cell, but I don't know how to pull the information based on the value of the week (which would be in cell B3 in the example above). Therefore when I change the week number (in B3 above) to 5 the sales in cell C3 would show 230.

Hope this is clear. Thanks in advance for any help.

 

[alansidman]

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]

Week​

[/td][td]

Dept A​

[/td][td]

Dept B​

[/td][td]

Dept C​

[/td][td]

Dept D​

[/td][td]

Dept E​

[/td][td]

Dept F​

[/td][td]

Dept G​

[/td][td]

​

[/td][td]

​

[/td][td]

Week​

[/td][td]

Week 3​

[/td][/tr]


[tr][td]

2​

[/td][td]

Week 1​

[/td][td]

700​

[/td][td]

520​

[/td][td]

100​

[/td][td]

620​

[/td][td]

44​

[/td][td]

120​

[/td][td]

800​

[/td][td]

​

[/td][td]

​

[/td][td]

Dept​

[/td][td]

Dept C​

[/td][/tr]


[tr][td]

3​

[/td][td]

Week 2​

[/td][td]

125​

[/td][td]

522​

[/td][td]

144​

[/td][td]

800​

[/td][td]

55​

[/td][td]

170​

[/td][td]

700​

[/td][td]

​

[/td][td]

​

[/td][td]

Sales​

[/td][td]

200​

[/td][/tr]


[tr][td]

4​

[/td][td]

Week 3​

[/td][td]

122​

[/td][td]

400​

[/td][td]

200​

[/td][td]

120​

[/td][td]

44​

[/td][td]

150​

[/td][td]

500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

5​

[/td][td]

Week 4​

[/td][td]

100​

[/td][td]

350​

[/td][td]

180​

[/td][td]

100​

[/td][td]

70​

[/td][td]

190​

[/td][td]

600​

[/td][td]

​

[/td][td]

​

[/td][td]

Formula in L3 below​

[/td][td]

​

[/td][/tr]


[tr][td]

6​

[/td][td]

Week 5​

[/td][td]

230​

[/td][td]

400​

[/td][td]

158​

[/td][td]

164​

[/td][td]

80​

[/td][td]

170​

[/td][td]

500​

[/td][td]

​

[/td][td]

​

[/td][td]

=INDEX($B$2:$H$6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,$B$1:$H$1,0))​

[/td][td]

​

[/td][/tr]
[/table]

 

[tlg]

Thanks for your reply.

This works great in a single sheet but I need to pull the information from another workbook (salesdata.xls). I have tried to edit the formula you provided but get stuck.

Thanks again!

 

[RasGhul]

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][COLOR=#000000]=INDEX([salesdata]Sheet1!$B$2:$H$6,MATCH(L1,[/COLOR][salesdata]Sheet1!$A$2:$A$6,[COLOR=#000000]0),MATCH(L2,[/COLOR][salesdata]Sheet1![COLOR=#000000]$B$1:$H$1,0))[/COLOR][/TD]
[/TR]
</tbody>[/TABLE]

Just carefully replace Sheet1! with "your sheet name!" if you have custom sheet names.