Sumproduct with Index / Match or Vlookup not returning array

[Ron0813]

Hello –
Similar to challenges others have described here, I’m having troubles with using Vlookup Index/Match along with Sumproduct. Those two functions don’t want to return an array in my formula.

Scenario:
I have an invoice table with an Invoice #, Item Code, Sales Price, and Quantity. Each Item Code is associated with a Category which is in a lookup table (not in the Invoice table). I do not want to create a helper column. I am trying to determine revenue for a given Category (For Category, multiply Sales Price by Quantity and sum across all invoices). I have been using the following formula, tables below as well. Invoice table on the left and look up table on the right. Not real data, I’m simplifying.

=SUMPRODUCT($C$2:$C$6,$D$2:$D$6,--(INDEX($G$2:$G$6,MATCH($B$2:$B$6,$F$2:$F$6,0))=$B$11))

I'm supplying the Category as an input (B11) and using True/False return from the Index formula (Array 3 in Sumproduct) to filter on the Category. That is, if it returns 0 the values in that row will not be summed.

Column A
[TABLE="width: 607"]
<tbody>[TR]
[TD]Invoice Number[/TD]
[TD]Item Code[/TD]
[TD]Sales Price Per Unit[/TD]
[TD]Quantity[/TD]
[TD][/TD]
[TD]Item Code[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]00001[/TD]
[TD]1[/TD]
[TD]34.95[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]00002[/TD]
[TD]1[/TD]
[TD]15.26[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]00003[/TD]
[TD]3[/TD]
[TD]26.95[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]00004[/TD]
[TD]3[/TD]
[TD]55.95[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]00005[/TD]
[TD]5[/TD]
[TD]4.95[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Thank you Dante! The expected result for B would actually be 2298.35.

That is correct, I did not read the requirement completely .


I vote for Marcelo's suggestions .

 

[Marcelo Branco]

That definitely works Marcelo. Thank you for this. I'm not that familiar with Lookup but will go learn about it and make sure this works in my larger dataset/problem. Thank you so much for this. Wish I had posted earlier, I think I spent 8 hours fooling with this. You just saved me a bunch of time.

You are welcome. Glad to help.

M.