Vlookup array formula problem

[Zach]

Hi
This is my first attempt at array formulas, and I'm not having much luck.

My formula is:
{=SUM(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 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.

For example, if the GOAFCF table has 2 lookup values of 3, and one lookup
value of 4, it will return respective values of 2,500*2 and 5,000*1, giving a
total of 10,000. The remaining lookup values are 1, and return a value of
zero.

What am I missing, or am I misunderstanding what array formulas can do?

Thanks in advance
Zach T

 

[ryguy7272]

You need to use sumproduct and you need to give more details; specific
ranges, specific worksheet names, data now and desired results.

Thanks,
Ryan---

 

[Zach]

Thanks for the reply Ryan. I'm not sure if you were meaning for me to give
more details in here, or needed in formula.

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 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

I should be getting a total result for this data of $10,000. 2 values of #3
returning $2500, and 1 value of #4 returning $5000, added together for
$10,000.

Does this help?
Zach.