Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Array formula using sumproduct & vlookup

140 views
Skip to first unread message

Zach

unread,
Oct 30, 2009, 11:02:03 AM10/30/09
to
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

unread,
Oct 30, 2009, 11:43:49 AM10/30/09
to

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

unread,
Oct 30, 2009, 12:01:01 PM10/30/09
to
VLOOKUP doesn't work with arrays.

Try this:

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

--
Biff
Microsoft Excel MVP


"Zach" <Za...@discussions.microsoft.com> wrote in message
news:FB68B123-F799-41E6...@microsoft.com...

Pete McCosh

unread,
Oct 30, 2009, 12:06:01 PM10/30/09
to
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

unread,
Oct 30, 2009, 12:22:01 PM10/30/09
to

"Pete McCosh" wrote:

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

unread,
Oct 30, 2009, 12:50:01 PM10/30/09
to

"T. Valko" wrote:

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

T. Valko

unread,
Oct 30, 2009, 1:13:04 PM10/30/09
to
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Zach" <Za...@discussions.microsoft.com> wrote in message

news:738BC036-1EEC-4102...@microsoft.com...

0 new messages