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:

Week	Dept A	Dept B	Dept C	Dept D	Dept E	Dept F	Dept G
Week 1	700	520	100	620	44	120	800
Week 2	125	522	144	800	55	170	700
Week 3	122	400	200	120	44	150	500
Week 4	100	350	180	100	70	190	600
Week 5	230	400	158	164	80	170	500

<tbody>
</tbody>

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

Dept A		Sales
Week Number:	4	100

<tbody>
</tbody>

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

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Week​

Dept A​

Dept B​

Dept C​

Dept D​

Dept E​

Dept F​

Dept G​

​

​

Week​

Week 3​

2​

Week 1​

700​

520​

100​

620​

44​

120​

800​

​

​

Dept​

Dept C​

3​

Week 2​

125​

522​

144​

800​

55​

170​

700​

​

​

Sales​

200​

4​

Week 3​

122​

400​

200​

120​

44​

150​

500​

​

​

​

​

5​

Week 4​

100​

350​

180​

100​

70​

190​

600​

​

​

Formula in L3 below​

​

6​

Week 5​

230​

400​

158​

164​

80​

170​

500​

​

​

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

​

 

[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.