Return Array from Index/Match?

[EB08]

I feel like i'm overlooking something, but my goal is this: without the use of a helper column, use either index/match or another lookup function to return an array of values to then be used in sumproduct() or an array sum(). I cant seem to get any of the html makers to work on the office computer, or i would show a quick example directly from the sheet.

This is how i started to create the formula (which does not work) if it helps anyone understand the direction i'm trying to go with this.

Let me know if i need to get more specific. Thanks.

{=sum(index(B1:B3,match(D1:D3,A1:A3,0))*E1:E3)}

 

[EB08]

Domenic - exactly what i'm after, thanks.

also, thanks everyone for your time and input; i realize i made this one more difficult than it had to be with my poor explanations.

 

[zuxev]

Hi Guys,

I have similar question with EB08 but have a little bit compplicated. Also have tried with Domenic solution but result N/A for me.

I try to get total ammount of the item purchased on certain date

Garbled image removed by Moderator

I have item price list on other table as follows:

Garbled image removed by Moderator

As item price are going changes, i need to sum the total ammount of item purchased based on price working on the purchase date.

For example, on the table there is transaction in 1st Jan and when look into the table, there is price update on 1st Jan, so the ammount will be 1*0,5 + 2*2 = 4,5
On 4 Feb, there is purchase transaction and when look into the tablem there is price update on 2nd Feb, so the ammount will be 2*0,75 + 2*5 = 11,5

Firstly i think i can use sumproduct to get the total ammount, but i stuck on how i can get the correct price for each item.

Any comment would be much appreciated

Thanks!

 

[zuxev]

Sorry for set wrongly for the images.

Here is images for data:
[TABLE="width: 411"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Supplier
[/TD]
[TD]Total
[/TD]
[TD]Toothbrush
[/TD]
[TD]Toothpaste
[/TD]
[TD]Towel
[/TD]
[/TR]
[TR]
[TD]01-Jan
[/TD]
[TD]XXX
[/TD]
[TD]4.5
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]01-Jan
[/TD]
[TD]YYY
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Feb
[/TD]
[TD]XXX
[/TD]
[TD]11.5
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]31-Jan
[/TD]
[TD]YYY
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And here is for lookup table:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item Name
[/TD]
[TD]Price
[/TD]
[TD]Last Update
[/TD]
[/TR]
[TR]
[TD]Toothbrush
[/TD]
[TD]0.5
[/TD]
[TD]01-Jan
[/TD]
[/TR]
[TR]
[TD]Toothpaste
[/TD]
[TD]1
[/TD]
[TD]01-Jan
[/TD]
[/TR]
[TR]
[TD]Towel
[/TD]
[TD]2
[/TD]
[TD]01-Jan
[/TD]
[/TR]
[TR]
[TD]Toothbrush
[/TD]
[TD]0.75
[/TD]
[TD]02-Feb
[/TD]
[/TR]
[TR]
[TD]Toothpaste
[/TD]
[TD]1.25
[/TD]
[TD]02-Feb
[/TD]
[/TR]
[TR]
[TD]Towel
[/TD]
[TD]5
[/TD]
[TD]02-Feb
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]