Array formula using sumproduct & vlookup

[Zach]

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

 

[Glenn]

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

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(RelImpact))))*RelImpact)

 

[T. Valko]

VLOOKUP doesn't work with arrays.

Try this:

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

 

[Pete McCosh]

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

 

[Zach]

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

 

[Zach]

VLOOKUP doesn't work with arrays.

Try this:

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

--
Biff
Microsoft Excel MVP





.

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

 

[T. Valko]

You're welcome. Thanks for the feedback!