Array formula using sumproduct & vlookup
 

Hi
I cant get my array formula to work. The answer that I am getting is just
$0. It is only looking at the first value in the GOAFCF table.

My formula is:
{=SUMPRODUCT(VLOOKUP(GOAFCF,RelImpact,9,FALSE))} (accepted with the CSE, not
just Enter)

I want to lookup up the values in each cell in the GOAFCF table (5 rows, 7
columns), compare them to the values in the RelImpact table , and add up the
returned values in the 9th column.

The tables (named ranges) are on different worksheets than the answer
worksheet.

The GOAFCF table has a value in each cell. The data in GOAFCF is:
1 1 1 1 1 1 1
1 1 1 1 1 1 4
1 1 1 1 1 1 1
1 1 1 1 1 1 3
1 1 1 1 1 1 3

The RelImpact table contains lookup values from 1 to 8 inclusive and merged
cells in columns 6 & 7 and 9 & 10. The lookup values are sorted ascending.
Each cell in the 9th column has a value in it, with one value being zero. The
data in the RelImpact table (simplified to 2 columns here) is:
1 $0
2 $1,000
3 $2,500
4 $5,000
5 $10,000
6 $25,000
7 $50,000
8 $100,000

With this example, I have 2 values of #3 returning $2500, and 1 value of #4
returning $5000, added together for $10,000. The remaining lookup values are
1, and return a value of zero.

If I use cell references instead of named ranges, I get the same result.
What am I missing?
TIA
Zach

Array formula using sumproduct & vlookup
 

Zach wrote:
Hi
I cant get my array formula to work. The answer that I am getting is just
$0. It is only looking at the first value in the GOAFCF table.

My formula is:
{=SUMPRODUCT(VLOOKUP(GOAFCF,RelImpact,9,FALSE))} (accepted with the CSE, not
just Enter)

I want to lookup up the values in each cell in the GOAFCF table (5 rows, 7
columns), compare them to the values in the RelImpact table , and add up the
returned values in the 9th column.

The tables (named ranges) are on different worksheets than the answer
worksheet.

The GOAFCF table has a value in each cell. The data in GOAFCF is:
1 1 1 1 1 1 1
1 1 1 1 1 1 4
1 1 1 1 1 1 1
1 1 1 1 1 1 3
1 1 1 1 1 1 3

The RelImpact table contains lookup values from 1 to 8 inclusive and merged
cells in columns 6 & 7 and 9 & 10. The lookup values are sorted ascending.
Each cell in the 9th column has a value in it, with one value being zero. The
data in the RelImpact table (simplified to 2 columns here) is:
1 $0
2 $1,000
3 $2,500
4 $5,000
5 $10,000
6 $25,000
7 $50,000
8 $100,000

With this example, I have 2 values of #3 returning $2500, and 1 value of #4
returning $5000, added together for $10,000. The remaining lookup values are
1, and return a value of zero.

If I use cell references instead of named ranges, I get the same result.
What am I missing?
TIA
Zach


With only the dollar values in the named range "RelImpact", the following array
formula (commit with CTRL+SHIFT+ENTER) will work:

=SUM(COUNTIF(GOAFCF,ROW(INDIRECT("1:"&COUNT(RelImp act))))*RelImpact)

Array formula using sumproduct & vlookup
 

VLOOKUP doesn't work with arrays.

Try this:

=SUMPRODUCT(LOOKUP(GOAFCF,INDEX(RelImpact,,1),INDE X(RelImpact,,9)))

--
Biff
Microsoft Excel MVP


"Zach" wrote in message
...
Hi
I can't get my array formula to work. The answer that I am getting is just
$0. It is only looking at the first value in the GOAFCF table.

My formula is:
{=SUMPRODUCT(VLOOKUP(GOAFCF,RelImpact,9,FALSE))} (accepted with the CSE,
not
just Enter)

I want to lookup up the values in each cell in the GOAFCF table (5 rows, 7
columns), compare them to the values in the RelImpact table , and add up
the
returned values in the 9th column.

The tables (named ranges) are on different worksheets than the answer
worksheet.

The GOAFCF table has a value in each cell. The data in GOAFCF is:
1 1 1 1 1 1 1
1 1 1 1 1 1 4
1 1 1 1 1 1 1
1 1 1 1 1 1 3
1 1 1 1 1 1 3

The RelImpact table contains lookup values from 1 to 8 inclusive and
merged
cells in columns 6 & 7 and 9 & 10. The lookup values are sorted ascending.
Each cell in the 9th column has a value in it, with one value being zero.
The
data in the RelImpact table (simplified to 2 columns here) is:
1 $0
2 $1,000
3 $2,500
4 $5,000
5 $10,000
6 $25,000
7 $50,000
8 $100,000

With this example, I have 2 values of #3 returning $2500, and 1 value of
#4
returning $5000, added together for $10,000. The remaining lookup values
are
1, and return a value of zero.

If I use cell references instead of named ranges, I get the same result.
What am I missing?
TIA
Zach

Array formula using sumproduct & vlookup
 

if you simply want to multiply the returned value from RelImpact by the
number of occurences in GOAFCF, there's no need to get so complex.

For the purposes of illustration, populate A1:A8 with 1 to 8, then B1 with
and copy down:

=Vlookup($A1,RelImpact,9,FALSE)*CountIf(GOAFCF,$A1 )

Cheers, Pete

Array formula using sumproduct & vlookup
 

"Pete McCosh" wrote:

if you simply want to multiply the returned value from RelImpact by the
number of occurences in GOAFCF, there's no need to get so complex.

For the purposes of illustration, populate A1:A8 with 1 to 8, then B1 with
and copy down:

=Vlookup($A1,RelImpact,9,FALSE)*CountIf(GOAFCF,$A1 )

Cheers, Pete

Thanks Pete, but I need it to total all 35 lookup cell answers into one cell
for a grand total. I could do this with more tables, but I thought that I
could do this with arrays or something else (trying to expand my Excel
knowledge here.)

Any other thoughts?
Zach

Array formula using sumproduct & vlookup
 

"T. Valko" wrote:

VLOOKUP doesn't work with arrays.

Try this:

=SUMPRODUCT(LOOKUP(GOAFCF,INDEX(RelImpact,,1),INDE X(RelImpact,,9)))

--
Biff
Microsoft Excel MVP


"Zach" wrote in message
...
Hi
I can't get my array formula to work. The answer that I am getting is just
$0. It is only looking at the first value in the GOAFCF table.

My formula is:
{=SUMPRODUCT(VLOOKUP(GOAFCF,RelImpact,9,FALSE))} (accepted with the CSE,
not
just Enter)

I want to lookup up the values in each cell in the GOAFCF table (5 rows, 7
columns), compare them to the values in the RelImpact table , and add up
the
returned values in the 9th column.

The tables (named ranges) are on different worksheets than the answer
worksheet.

The GOAFCF table has a value in each cell. The data in GOAFCF is:
1 1 1 1 1 1 1
1 1 1 1 1 1 4
1 1 1 1 1 1 1
1 1 1 1 1 1 3
1 1 1 1 1 1 3

The RelImpact table contains lookup values from 1 to 8 inclusive and
merged
cells in columns 6 & 7 and 9 & 10. The lookup values are sorted ascending.
Each cell in the 9th column has a value in it, with one value being zero.
The
data in the RelImpact table (simplified to 2 columns here) is:
1 $0
2 $1,000
3 $2,500
4 $5,000
5 $10,000
6 $25,000
7 $50,000
8 $100,000

With this example, I have 2 values of #3 returning $2500, and 1 value of
#4
returning $5000, added together for $10,000. The remaining lookup values
are
1, and return a value of zero.

If I use cell references instead of named ranges, I get the same result.
What am I missing?
TIA
Zach



.

Thanks Biff. Works great. It wasn't even an array formula.
Zach

Array formula using sumproduct & vlookup
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Zach" wrote in message
...


"T. Valko" wrote:

VLOOKUP doesn't work with arrays.

Try this:

=SUMPRODUCT(LOOKUP(GOAFCF,INDEX(RelImpact,,1),INDE X(RelImpact,,9)))

--
Biff
Microsoft Excel MVP


"Zach" wrote in message
...
Hi
I can't get my array formula to work. The answer that I am getting is
just
$0. It is only looking at the first value in the GOAFCF table.

My formula is:
{=SUMPRODUCT(VLOOKUP(GOAFCF,RelImpact,9,FALSE))} (accepted with the
CSE,
not
just Enter)

I want to lookup up the values in each cell in the GOAFCF table (5
rows, 7
columns), compare them to the values in the RelImpact table , and add
up
the
returned values in the 9th column.

The tables (named ranges) are on different worksheets than the answer
worksheet.

The GOAFCF table has a value in each cell. The data in GOAFCF is:
1 1 1 1 1 1 1
1 1 1 1 1 1 4
1 1 1 1 1 1 1
1 1 1 1 1 1 3
1 1 1 1 1 1 3

The RelImpact table contains lookup values from 1 to 8 inclusive and
merged
cells in columns 6 & 7 and 9 & 10. The lookup values are sorted
ascending.
Each cell in the 9th column has a value in it, with one value being
zero.
The
data in the RelImpact table (simplified to 2 columns here) is:
1 $0
2 $1,000
3 $2,500
4 $5,000
5 $10,000
6 $25,000
7 $50,000
8 $100,000

With this example, I have 2 values of #3 returning $2500, and 1 value
of
#4
returning $5000, added together for $10,000. The remaining lookup
values
are
1, and return a value of zero.

If I use cell references instead of named ranges, I get the same
result.
What am I missing?
TIA
Zach



.

Thanks Biff. Works great. It wasn't even an array formula.
Zach