Help with Array Formula with Embedded Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two tables with two columns each which I am displaying below in csv
format:

Table # 1:

Store, Sales
A, 5000
B, 4000
C, 6000
D, 2000

Table # 2:

Store, Region
1,North
2,South
3,North

I need to fill a table with the following format:

Region, Total Sales
North, <Total sales for North>
South, <Total sales for South>

I need hep with the <Total sales formulas>. I know a workaround is to create
an additional column next to Table 1 to display the Region and then use
SUMIF. However, this is not something that I can do in this situation. I
tried the following formula, with A8="North":

=SUM(IF(VLOOKUP(Table1_Col1,Table2,2,0)=A8,Table1_Col2,0))

However, this formula gives me 17,000 as result (instead of 11,000). If I
replicate the formula row by row (in non-array format), it does work...

Any help is greatly appreciated! But please remember that I need only one
formula in one cell.

Thanks!

Lucas
 
Hi
how do you match store IDs (A,B,C,D) with your region (I did not see
any mathcing tabnle for this)
 
Please see below. Table 2 should have had Stores A, B and C instead of 1, 2
and 3. Thanks in advance for your help.

Lucas
 
Hi
if on the third sheet cell A1 stores the region try the following
formula
=SUMPRODUCT(--('sheet1'!$A$1:$A$100=INDEX('sheet2'!$A$1:$A$100,MATCH(A1
,'sheet2'!$B$1:$B$100,0))),'sheet1'!$B$1:$B$100)
 
Perfect, Frank. Thanks a lot!

Frank Kabel said:
Hi
if on the third sheet cell A1 stores the region try the following
formula
=SUMPRODUCT(--('sheet1'!$A$1:$A$100=INDEX('sheet2'!$A$1:$A$100,MATCH(A1
,'sheet2'!$B$1:$B$100,0))),'sheet1'!$B$1:$B$100)
 
Frank, I thought it worked for a minute, but it didn't. Maybe I am doing
something wrong.

Here's what I have:

In Sheet1, starting in A1:

Column A Column B
A 5000
B 4000
C 6000
D 2000

In Sheet2, starting in A1:
Column A Column B
A North
B South
C North
D East

In Sheet 3, starting in A1:
Column A Column B Column B Result:
North Your formula 5000
South Your formula 4000
East Your formula 2000

Your formula:
=SUMPRODUCT(--(Sheet1!$A$1:$A$100=INDEX(Sheet2!$A$1:$A$100,MATCH(A1,Sheet2!$
B$1:$B$100,0))),Sheet1!$B$1:$B$100)

This looks ok at first glance. However, I would have expected Sheet3!A1 to
be equal to 11,000 instead of 5,000 (note that both A and C are in 2000). Am
I missing something?

Thanks,

Lucas
 
Hi
try the following:
1. On sheet 1 add a helper column which will store the region code.
Enter the following in C1
=VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0)
and copy this down

2. now use the following formula in sheet 3
=SUMPRODUCT(--(Sheet1!$C$1:$C$100=A1),Sheet1!$B$1:$B$100)
 
Thanks for the suggestion, Frank. However, that will not work for me in this
case...

Do you know if there is any way to make it work without the helper formula?

Thanks!

Lucas
 
Back
Top