Lookup multiple values in the same column (I think this is what I'm looking for)

[samvince]

Hi everyone,

I'm quite new to excel and have been trawling the internet and YouTube for answers but couldn't find what I was looking for exactly.

So I have a transaction sheet from Amazon for accounting purposes. All I want to see is how much I had made per item. However, the way Amazon show it is a bunch of fees and how much you have been paid.

Order ID	SKU	Transaction type	Payment Type	Payment Detail	Amount	Quantity	Product Title
222-3312-1819	AS-TTYY-HSWR	Order Payment	Amazon fees	Commission	-£2.44		Primer [DVD]
222-3312-1820	AS-TTYY-HSWR	Order Payment	Amazon fees	FBA fulfilment fee per unit	-£1.94		Primer [DVD]
222-3312-1821	AS-TTYY-HSWR	Order Payment	Amazon fees	Shipping chargeback	-£2.99		Primer [DVD]
222-3312-1822	AS-TTYY-HSWR	Order Payment	Amazon fees	Variable closing fee	-£0.60		Primer [DVD]
222-3312-1823	AS-TTYY-HSWR	Order Payment	Product charges		£13.50	1	Primer [DVD]
222-3312-1824	AS-TTYY-HSWR	Order Payment	Other	Shipping	£2.99		Primer [DVD]

<tbody>
</tbody>

So as you can see with Amazon they have multiple transactions for 1 item, instead of just telling you how much you have made after fees. So this is what I am trying to figure out. Generally there are hundreds of transactions over the month and I cannot figure out how to subtract all the fees in red from the final amount of money coming in, in column F. Ideally I'd like something similar to something below which will tell me exactly how much I have made for each item.

Product Title	All Fees	Payments/Sale	Net Profit after all fees

<tbody>
</tbody>

If you could point me in the right direction or have any suggestions of how best to do this I would greatly appreciate it.

Many thanks

 

[Michael M]

How are the multiple transactions tied to the one Item ?....by SKU ?

 

[samvince]

Hi thanks for replying. It would be SKU and Order ID if that helps

 

[Fluff]

Hi & welcome to MrExcel
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Order ID	SKU	Transaction type	Payment Type	Payment Detail	Amount	Quantity	Product Title		Order ID	Product Title	All Fees	Payments/Sale	Net Profit
2	222-3312-1819	AS-TTYY-HSWR	Order Payment	Amazon fees	Commission	-2.44		Primer [DVD]		222-3312	Primer [DVD]	-7.97	16.49	8.52
3	222-3312-1820	AS-TTYY-HSWR	Order Payment	Amazon fees	FBA fulfilment fee per unit	-1.94		Primer [DVD]		222-3313	Video	-9.64	18.5	8.86
4	222-3312-1821	AS-TTYY-HSWR	Order Payment	Amazon fees	Shipping chargeback	-2.99		Primer [DVD]
5	222-3312-1822	AS-TTYY-HSWR	Order Payment	Amazon fees	Variable closing fee	-0.6		Primer [DVD]
6	222-3312-1823	AS-TTYY-HSWR	Order Payment	Product charges		13.5	1	Primer [DVD]
7	222-3312-1824	AS-TTYY-HSWR	Order Payment	Other	Shipping	2.99		Primer [DVD]
8	222-3313-2019	AS-TTYY-HSWR	Order Payment	Amazon fees	Commission	-3.5		Video
9	222-3313-2020	AS-TTYY-HSWR	Order Payment	Amazon fees	FBA fulfilment fee per unit	-0.54		Video
10	222-3313-2021	AS-TTYY-HSWR	Order Payment	Amazon fees	Shipping chargeback	-5		Video
11	222-3313-2022	AS-TTYY-HSWR	Order Payment	Amazon fees	Variable closing fee	-0.6		Video
12	222-3313-2023	AS-TTYY-HSWR	Order Payment	Product charges		13.5	2	Video
13	222-3313-2024	AS-TTYY-HSWR	Order Payment	Other	Shipping	5		Video
Quote

Cell Formulas
Range	Formula
L2	=SUMPRODUCT((LEFT($A$2:$A$13,8)=$J2)*($F$2:$F$13<0),($F$2:$F$13))
M2	=SUMPRODUCT((LEFT($A$2:$A$13,8)=$J2)*($F$2:$F$13>0),($F$2:$F$13))
N2	=M2+L2
J2	{=IFERROR(INDEX(LEFT($A$2:$A$13,8),MATCH(0,COUNTIF(J$1:J1,LEFT($A$2:$A$13,8)),0)),"")}
K2	{=IFERROR(INDEX($H$2:$H$13,MATCH(J2,LEFT($A$2:$A$13,8),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.