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

Invoice Number	Item Code	Sales Price Per Unit	Quantity		Item Code	Category
00001	1	34.95	11		1	D
00002	1	15.26	7		2	B
00003	3	26.95	23		3	B
00004	3	55.95	30		4	C
00005	5	4.95	11		5	D

<tbody>
</tbody>

 

[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.