Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP in SUMPRODUCT array
I am trying to convert a list of payments in this format:
Date | Account Code | Amount into a quarterly cash flow categorised by Account Code type. Im trying to use VLOOKUP to convert the Account code into a category so that I can split the cash flow into 3 categories. I have a lookup table which relates each Account Code to one of three categories. Im using: =SUMPRODUCT((Interest!$A$6:$A$256=E$4)*(Interest! $A$6:$A$256<=E$5)*(VLOOKUP(VALUE(Interest!$C$6:$C$ 256),Analysis!$A$5:$G$75,7)=$A$8),(Interest!$F$6:$ F$256)) The list of payments is on a sheet called interest. A6:A256 contains the dates, E4 & E5 contain the start & end dates for each quarter, C6:C256 contains the account codes (irritatingly held as text), Analysis!A5:G75 contains the lookup table, A8 contains the category I want to look up, and F6:F256 contains the amounts. This formula successfully sums between the dates but doesnt distinguish the categories. What am I doing wrong?! |
#2
|
|||
|
|||
If each item in Interest!C6:C256 exists in Analysis!A5:A75, try...
=SUMPRODUCT(--(Interest!$A$6:$A$256=E$4),--(Interest!$A$6:$A$256<=E$5),- -(LOOKUP(Interest!$C$6:$C$256,Analysis!$A$5:$G$75)= $A$8),Interest!$F$6:$F$256) If Interest!C6:C256 may contain items that do not exist in Analysis!A5:A75, try... =SUMPRODUCT(--(Interest!$A$6:$A$256=E$4),--(Interest!$A$6:$A$256<=E$5),- -(ISNUMBER(MATCH(Interest!$C$6:$C$256,Analysis!$A$5 :$A$75,0))),--(ISNUMBE R(1/(LOOKUP(Interest!$C$6:$C$256,Analysis!$A$5:$G$75)= $A$8))),Interest!$F$6:$F$256) Hope this helps! In article , "KM01" wrote: I am trying to convert a list of payments in this format: Date | Account Code | Amount into a quarterly cash flow categorised by Account Code type. Im trying to use VLOOKUP to convert the Account code into a category so that I can split the cash flow into 3 categories. I have a lookup table which relates each Account Code to one of three categories. Im using: =SUMPRODUCT((Interest!$A$6:$A$256=E$4)*(Interest! $A$6:$A$256<=E$5)*(VLOOKUP(V ALUE(Interest!$C$6:$C$256),Analysis!$A$5:$G$75,7)= $A$8),(Interest!$F$6:$F$256) ) The list of payments is on a sheet called interest. A6:A256 contains the dates, E4 & E5 contain the start & end dates for each quarter, C6:C256 contains the account codes (irritatingly held as text), Analysis!A5:G75 contains the lookup table, A8 contains the category I want to look up, and F6:F256 contains the amounts. This formula successfully sums between the dates but doesnt distinguish the categories. What am I doing wrong?! |
#3
|
|||
|
|||
Hi there, thanks for your help. I making progress but havent got there yet.
Niether of your suggestions worked straight off, but parts of the 2nd one seem to get me almost there. I didnt understand the 1/LOOKUP part of the your suggestion though. Im now using: {=SUMPRODUCT(--(Interest!$A$6:$A$256=E$4),--(Interest!$A$6:$A$256<=E$5),--(INDEX(Analysis!$A$5:$G$75,MATCH(VALUE(Interest!$C $6:$C$256),Analysis!$A$5:$A$75,0),7)=$A$8)*Interes t!$F$6:$F$256)} Having worked out how to use Excel to evaluate formulae (really useful function!) I can see that the whole array is resolving correctly except the =index() bit which is evaluating the first row in C6:C256 to TRUE without looking at the subsequent rows and consequently summing the whole of F6:F256. How do I get this bit to behave like an array formula? Thanks again! |
#4
|
|||
|
|||
Does it help if you change this part of the formula...
Interest!$C$6:$C$256 to Interest!$C$6:$C$256+0 In article , "KM01" wrote: Hi there, thanks for your help. I making progress but havent got there yet. Niether of your suggestions worked straight off, but parts of the 2nd one seem to get me almost there. I didnt understand the 1/LOOKUP part of the your suggestion though. Im now using: {=SUMPRODUCT(--(Interest!$A$6:$A$256=E$4),--(Interest!$A$6:$A$256<=E$5),--(IN DEX(Analysis!$A$5:$G$75,MATCH(VALUE(Interest!$C$6: $C$256),Analysis!$A$5:$A$75, 0),7)=$A$8)*Interest!$F$6:$F$256)} Having worked out how to use Excel to evaluate formulae (really useful function!) I can see that the whole array is resolving correctly except the =index() bit which is evaluating the first row in C6:C256 to TRUE without looking at the subsequent rows and consequently summing the whole of F6:F256. How do I get this bit to behave like an array formula? Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Indexing an Array with VLOOKUP | Excel Worksheet Functions | |||
Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |