Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP -- Table Array Problem DD14128 Excel Worksheet Functions 1 June 19th 08 03:23 PM
Array formula problem Constance Excel Discussion (Misc queries) 3 August 22nd 06 06:03 PM
Problem w/Array Formula danw Excel Worksheet Functions 2 August 1st 06 10:55 PM
Problem with Vlookup array selection Scott269 Excel Worksheet Functions 2 January 30th 06 06:29 PM
array formula problem bill gras Excel Worksheet Functions 6 September 28th 05 04:54 AM


All times are GMT +1. The time now is 09:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"