Vlookup array formula problem
 

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

Vlookup array formula problem
 

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

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Zach" wrote:

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

Vlookup array formula problem
 

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.


"ryguy7272" wrote:

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

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Zach" wrote:

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