VLookup data from more than one row

[marcidee]

I have a spreadsheet that has one tab showing stock levels and item costs and another tab showing sales - the sales tab picks up the items and sales prices from the stock sheet etc and I want to return data the sales tab to the stock tab showing quantity sold.

I am using the following formula in the qty sold column - however if the same item is sold on more than one row it only picks up the number from the first row - can someone help me adjust the formula so that it picks up the data on every row this appears.

=IFERROR(VLOOKUP(A2,Sales!$B$2:$D$30,3,FALSE),0)

ie in the example below the qty column in the stock sheet only shows sales of 4 and not 6

03/01/2022​	Amazon Echo	17.00​	4​
09/01/2022​	Amazon Echo	17.00​	2​

Thank you for your help
Marc

 

[Toadstool]

Hi Marcidee,

You can use SUMIF

Marcidee.xlsx
	A	B	C	D
1	Date	Product	Cost	Qty
2	3/1/2022	Amazon Echo	17	4
3	9/1/2022	Amazon Echo	17	2
Sales

Marcidee.xlsx
	A	B
1	Product
2	Amazon Echo	6
Stock

Cell Formulas
Range	Formula
B2	B2	=SUMIF(Sales!B2:$B$30,A2,Sales!$D$2:$D$30)

 

[marcidee]

Hi Toadstool
Thank you very much for your response - I appear to be getting inconsistent storage results using this formula - in the 2 screen shots below you will see Amazon Echo shows as sold 5 which is correct, however Anker Nebula shows 4 but should be 7, and Bayliss straightener shows 2 but should be 1?

StockName	Column1	Item Cost	Mark up	Sale Price	Qty Bought	Sold	Stock	Value of Stock
Amazon Echo	Active	£17.00​	£2.00​	£19.00​	20​	6​	14​	238​
Anker Nebula	Active	£319.99​	£30.00​	£349.99​	15​	4​	11​	3520​
BaByliss Berry Crush Straightener	Active	£28.00​		£28.00​	2​	2​	0​	0​
Baylis & Harding Jojoba, Vanilla & Almond Oil Luxury Bathing Duo Stack Gift Box Gift Set - Vegan Friendly…	Active	£7.49​		£7.49​	10​	2​	8​	60​
Bush Big LED Alarm Clock Radio	Active	£14.99​		£14.99​	3​	0​	3​	45​
Canon MG3650S WH…	Active	£39.99​		£39.99​	1​	2​	-1​	-40​
Canon PIXMA TR4551	Active	£49.99​		£49.99​	0​	0​	0​	0​

Date	Stock Name	Price	Sold QTY	Value
03/01/2022​	Amazon Echo	19.00​	4​	76.00​
09/01/2022​	Amazon Echo	19.00​	2​	38.00​
06/01/2022​	Anker Nebula	349.99​	2​	699.98​
15/02/2022​	Anker Nebula	349.99​	5​	1749.95​
07/01/2022​	BaByliss Berry Crush Straightener	28.00​	1​	28.00​
08/01/2022​	Baylis & Harding Jojoba, Vanilla & Almond Oil Luxury Bathing Duo Stack Gift Box Gift Set - Vegan Friendly…	7.49​	1​	7.49​
02/03/2022​	Canon MG3650S WH…	39.99​	1​	39.99​
10/01/2022​	CLARINS Body Care Set 3Pc	23.10​	1​	23.10​

 

[Toadstool]

It's working for me:

Marcidee2.xlsx
	A	B	C	D	E
1	Date	Stock Name	Price	Sold QTY	Value
2	3/1/2022	Amazon Echo	19	4	76
3	9/1/2022	Amazon Echo	19	2	38
4	6/1/2022	Anker Nebula	350	2	699.98
5	15/02/2022	Anker Nebula	350	5	1750
6	7/1/2022	BaByliss Berry Crush Straightener	28	1	28
7	8/1/2022	Baylis & Harding Jojoba, Vanilla & Almond Oil Luxury Bathing Duo Stack Gift Box Gift Set - Vegan Friendly…	7.49	1	7.49
8	2/3/2022	Canon MG3650S WH…	39.99	1	39.99
9	10/1/2022	CLARINS Body Care Set 3Pc	23.1	1	23.1
Sales

Marcidee2.xlsx
	A	B	C	D	E	F	G	H	I
1	StockName	Column1	Item Cost	Mark up	Sale Price	Qty Bought	Sold	Stock	Value of Stock
2	Amazon Echo	Active	17	2	19	20	6	14	238
3	Anker Nebula	Active	319.99	30	349.99	15	7	11	3520
4	BaByliss Berry Crush Straightener	Active	28		28	2	1	0	0
5	Baylis & Harding Jojoba, Vanilla & Almond Oil Luxury Bathing Duo Stack Gift Box Gift Set - Vegan Friendly…	Active	7.49		7.49	10	1	8	60
6	Bush Big LED Alarm Clock Radio	Active	14.99		14.99	3	0	3	45
7	Canon MG3650S WH…	Active	39.99		39.99	1	1	-1	-40
8	Canon PIXMA TR4551	Active	49.99		49.99	0	0	0	0
Stock

Cell Formulas
Range	Formula
G2:G8	G2	=SUMIF(Sales!$B$2:$B$9999,A2,Sales!$D$2:$D$9999)

 

[marcidee]

Thanks you - it's working for me too now