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