Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP -- Table Array Problem | Excel Worksheet Functions | |||
Array formula problem | Excel Discussion (Misc queries) | |||
Problem w/Array Formula | Excel Worksheet Functions | |||
Problem with Vlookup array selection | Excel Worksheet Functions | |||
array formula problem | Excel Worksheet Functions |