Unable to find my mistake

[rush2rajen]

Hi Excel Masters
I am stuck in a formula. I had written a index match as well as vlookup formula to get the desired result but its not showing the correct result.

Can anyone please help on this.

Thanks
Raj

 

[chirayu]

For values use SUMIF
For text you need CONCATENATE. But this is manual & doen't take into account criteria such as "HK 2". There is code out there to do a CONCATENATEIF (example link below)
http://www.extendoffice.com/documents/excel/2723-excel-concatenate-based-on-criteria.html