If a list contains a specific value fill with adjacent cell

[a cow tent]

Hello All,

I'm trying to find a quicker way to complete commission reports.
What formula can I put in the highlighted cells on sheet 2 to pull the quantity sold from sheet 1?

The concept I'm seeing is:
If the item number column on sheet 1 contains a specific value, take its adjacent cell's value in the quantity sold column and put it in the highlighted cell on sheet 2.
If the item number column on sheet 1 does not contain the specific value, put 0 in the highlighted cell on sheet 2.

I am open to a simpler concept, but I just want to paste my raw data on sheet 1 and have the report on sheet 2 fill itself in.
There are roughly 300 item numbers, and each salesman has anywhere from 1 to 60 specific item numbers he can gain commission from.
The commission prices are different from what's on the raw data report, so only item number and quantity sold are relevant.

Raw Data (Sheet 1)

Salesman Commission Summary (Sheet 2)

 

[awoohaw]

A VLOOKUP should be all you need:

Mr Excel Questions.xlsx
	A	B	C	D	E
1
2	Item Number	Quantity Sold	Price %	Commission
3	1	5	5%	0.2500
4	2	1	2%	0.0200
5	3	4	3%	0.1200
6
7
SalesCom2

Cell Formulas
Range	Formula
B3:B5	B3	=VLOOKUP(A3,SalesCom1!$A$2:$B$11,2,0)
D3:D5	D3	=B3*C3

Mr Excel Questions.xlsx
	A	B	C	D
1	Item Number	Quantity Sold
2	1	5
3	2	1
4	3	4
5	4	8
6	5	2
7	6	3
8	7	4
9	8	9
10	9	5
11	10	0
12
SalesCom1

 

[a cow tent]

A VLOOKUP should be all you need:

Mr Excel Questions.xlsx
	A	B	C	D	E
1
2	Item Number	Quantity Sold	Price %	Commission
3	1	5	5%	0.2500
4	2	1	2%	0.0200
5	3	4	3%	0.1200
6
7
SalesCom2

Cell Formulas
Range	Formula
B3:B5	B3	=VLOOKUP(A3,SalesCom1!$A$2:$B$11,2,0)
D3:D5	D3	=B3*C3

Mr Excel Questions.xlsx
	A	B	C	D
1	Item Number	Quantity Sold
2	1	5
3	2	1
4	3	4
5	4	8
6	5	2
7	6	3
8	7	4
9	8	9
10	9	5
11	10	0
12
SalesCom1

Thank you, awoohaw!

 

[a cow tent]

If it helps anyone else, I changed the formula to =IFERROR(VLOOKUP(A3,Sheet1!$A$2:$B$11,2,0),0) so it displays 0's instead of N/A's.

 

[Peter_SSs]

If it helps anyone else, I changed the formula to =IFERROR(VLOOKUP(A3,Sheet1!$A$2:$B$11,2,0),0) so it displays 0's instead of N/A's.

There is a specific function to deal with #N/A errors (& it's shorter to type than IFERROR. )

=IFNA(VLOOKUP(A3,Sheet1!$A$2:$B$11,2,0),0)