PedroTrigo
New Member
- Joined
- Jan 8, 2019
- Messages
- 3
Hello all!
I need some help using VLOOKUP (or other function) to sum repeating values. Below is some data as an example.
My goal is to look up the value in column 'Material' and sum the corresponding quantity for 'Material 2'. The Vlookup function works fine, except when there are repeating values. In the table below I highlighted one of those exceptions.
Im using the formula - (VLOOKUP(A4;A:D;4;FALSE)) - which only returns the first value: 283. The result im trying to get is 600 (283+317)
Is there any way to do this? Maybe add a VLOOKUP from down to up to the formula?
Would very much appreciate some help with this problem. Thanks in advance!
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Material
[/TD]
[TD]Line
[/TD]
[TD]Material 2
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD="align: center"]16127
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.70.010
[/TD]
</tbody>[/TD]
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.90.001
[/TD]
</tbody>[/TD]
[TD="align: center"]16127
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl70, width: 113"] 14110.71.001
[/TD]
</tbody>[/TD]
[TD][TABLE="align: center"]
<tbody>[TR]
[TD]16127
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl70, width: 113"] 10080.40.022
[/TD]
</tbody>[/TD]
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD][TABLE="align: center"]
<tbody>[TR]
[TD]16127
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.40.023
[/TD]
</tbody>[/TD]
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD][TABLE="align: center"]
<tbody>[TR]
[TD]16127
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.40.028
[/TD]
</tbody>[/TD]
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.70.301
[/TD]
</tbody>[/TD]
[TD]317
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.70.501
[/TD]
</tbody>[/TD]
[TD]317
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"] 14110.71.001
[/TD]
</tbody>[/TD]
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl70, width: 113"] 10080.40.022
[/TD]
</tbody>[/TD]
[TD]317
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>[/TD]
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.40.023
[/TD]
</tbody>[/TD]
[TD]317
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need some help using VLOOKUP (or other function) to sum repeating values. Below is some data as an example.
My goal is to look up the value in column 'Material' and sum the corresponding quantity for 'Material 2'. The Vlookup function works fine, except when there are repeating values. In the table below I highlighted one of those exceptions.
Im using the formula - (VLOOKUP(A4;A:D;4;FALSE)) - which only returns the first value: 283. The result im trying to get is 600 (283+317)
Is there any way to do this? Maybe add a VLOOKUP from down to up to the formula?
Would very much appreciate some help with this problem. Thanks in advance!
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Material
[/TD]
[TD]Line
[/TD]
[TD]Material 2
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD="align: center"]16127
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.70.010
[/TD]
</tbody>
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.90.001
[/TD]
</tbody>
[TD="align: center"]16127
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl70, width: 113"] 14110.71.001
[/TD]
</tbody>
[TD][TABLE="align: center"]
<tbody>[TR]
[TD]16127
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl70, width: 113"] 10080.40.022
[/TD]
</tbody>
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD][TABLE="align: center"]
<tbody>[TR]
[TD]16127
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.40.023
[/TD]
</tbody>
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD][TABLE="align: center"]
<tbody>[TR]
[TD]16127
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.40.028
[/TD]
</tbody>
[TD]283
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.70.301
[/TD]
</tbody>
[TD]317
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.70.501
[/TD]
</tbody>
[TD]317
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"] 14110.71.001
[/TD]
</tbody>
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl70, width: 113"] 10080.40.022
[/TD]
</tbody>
[TD]317
[/TD]
[/TR]
[TR]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]14110.71.001
[/TD]
</tbody>
[TD="align: center"]16128
[/TD]
[TD]
<colgroup><col width="113"></colgroup><tbody>
[TD="class: xl69, width: 113"]10080.40.023
[/TD]
</tbody>
[TD]317
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]