# VLOOKUP in SUMPRODUCT array

G

#### Guest

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. Iâ€™m 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.

Iâ€™m 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 doesnâ€™t distinguish the
categories. What am I doing wrong?!

D

#### Domenic

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!

G

#### Guest

Hi there, thanks for your help. I making progress but havenâ€™t got there yet.
Niether of your suggestions worked straight off, but parts of the 2nd one
seem to get me almost there. I didnâ€™t understand the 1/LOOKUP part of the

Iâ€™m 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)*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!

D

#### Domenic

Does it help if you change this part of the formula...

Interest!\$C\$6:\$C\$256

to

Interest!\$C\$6:\$C\$256+0