Vlookup with multiple entries in each cell

[zeta]

I have a spreadsheet with a vlookup table which returns a value in a column from a pivot table on another spreadsheet. However, the lookup value value have numeric values in multiple rows and with my vlookup formula it only returns the first value in the table array. Is there a formula or mod to the vlookup formula to allow this to work on both the cells and return as one value? E.g. Could INV0000238 have a formula that returns
70 000 000 in one cell instead of 64 000 000 and have have to manually add the 6 000 000 cell?
=vlookup("INV000235";'Sheet TEST'!$A$2:$B$4;2;FALSE) this is the formula I use now and returns 64 000 000.



Sheet TEST
A B

Investor No.	Value
INV0000235	64 000 000​
INV0000235	6 000 000
INV0000355	50 000 000

 

[gargamalebarbosa]

Try this one.

 

[zeta]

Try this one.

View attachment 70882

Works perfectly, thanks for the help!