Vlookup summing multiple values in one cell

[dbagnell]

Hi,

I've searched the archives and though I've found similar issues I haven't been able to figure this one out. I have a list of IDs in column A on worksheet 1. One worksheet 2 I have a vlookup returning modality and length, but in cases where there is more than one modality or length I can't get them concatenated (modality) or summed (length). The summing is the biggest thing I want to solve for, modality will just be a bonus.

I see that I can do this with an array formula, but since I have 648 rows of values I can't use a formula that has {1,2,3,4,5... all the way to 648}. I can't see another way of doing it. I'm not sure if I need to do something with index or match? Can anyone help?

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]MODALITY[/TD]
[TD]LENGTH[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]LMS[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]LMS[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]LOR[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

=vlookup(A2,table1,2,false)
=vlookup(A2,table1,3,false)

Thanks!

 

[sergioMabres]

Hi Dbagnell
You could use =SUMIF(Sheet1!A2:A4000,A2,Sheet1!C2:C4000) in Sheet2 for the sum of length
And you could use the UDF CusVlookup you can find the UDF in https://www.extendoffice.com/documents/excel/3309-excel-vlookup-multiple-values-concatenated.html
Cheers
Sergio

 

[rmb4466]

From what I'm understanding, I think SUMIF would take care of your summing for length: =sumif(table1_id_column,a2,table1_length_column)

 

[dbagnell]

Hi Dbagnell
You could use =SUMIF(Sheet1!A2:A4000,A2,Sheet1!C2:C4000) in Sheet2 for the sum of length
And you could use the UDF CusVlookup you can find the UDF in https://www.extendoffice.com/documents/excel/3309-excel-vlookup-multiple-values-concatenated.html
Cheers
Sergio

Wow, this worked! THANK YOU!!

 

[sergioMabres]

Glad to help