To VLOOKUP more than one possible match

[zuohong]

Hi all

I have a senario.

Worksheet 1

Part Quantity
A 20
B 14
C 9
D 19
B 31
G 12
K 10
E 32
B 17
.. .
.. .
.. .
C 43

Worksheet 2

Part Total
A 20
B 62
C 52
.. .
.. .
.. .

I need total quantities in worksheet 2 which summarizes all matching
part number (A,B...) in worksheet 1. If there is only one possible
match, I can use VLOOKUP. But now I need a sum. Please give me some
idea. Thanks in advance.

Best, YU

 

[Guest]

One way is to use SUMIF

Source data assumed in Sheet1,
cols A and B, data from row2 down

In Sheet2, with the uniques in Sheet1's col A listed in A2 down
Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)
Copy down

 

[Bondi]

Hi all

I have a senario.

Worksheet 1

Part Quantity
A 20
B 14
C 9
D 19
B 31
G 12
K 10
E 32
B 17
. .
. .
. .
C 43

Worksheet 2

Part Total
A 20
B 62
C 52
. .
. .
. .

I need total quantities in worksheet 2 which summarizes all matching
part number (A,B...) in worksheet 1. If there is only one possible
match, I can use VLOOKUP. But now I need a sum. Please give me some
idea. Thanks in advance.

Best, YU

Hi,

Maybe you can use SUMIF(). So if you have your list of parts in A1:A20
and quantities in B1:B20 on Sheet1 you can put something like this on
Sheet2:

=SUMIF(Sheet1!A1:A20,"A",Sheet1!B1:B20)

Regards,
Bondi

 

[zuohong]

Hi Bondi and Max

Thank you so much! It works well.

Best, Yu

 

[Guest]

You are welcome, Yu !