UDF to Vlookup multiple delimited values (numeric/non-numeric) and sum found values

Dear experts,

First time post here, but this forum has thaught me a lot and provided lots of answers in the past - thank you!
Now I have something for which I ahve not seen a solution here, similar topics have been posted but I am not succeeding in modifying them to suit my needs.

What I am after is a multivlookup that can lookup multiple values (delimited with ";" in this case) in one cell and then perform a sum on the resulting values.
I have attached an example to show better what I mean. The example is using realistic values, I have to create the total of the amounts for PO numbers that are entered by my colleagues. It is these PO numbers that are entered in one cell, delimited with ";". The delimiter is of no importance if it causes issues towards the solution, but I'd like to avoid commonly used special characters as much as possible (PO numbers are creative to say the least).

If you have any questions on this please let me know, thank you so much for having a look!


Very best regards,
GeertPO_SUM-Example.xlsx

Hi Geert,
try it

Please Login or Register  to view this content.

Hello Nilem,

That works absolutely perfectly, and it even has luxury with the optional delimiter! Amazing, and so quickly :-)
I have to say that I make my own UDF's from time to time but the one you wrote goes far beyond what I would be able to do (I adapted the MultiVlookup on this forum but could not get it to do both numeric and non-numeric).

Thanks a whole lot!!
Very best regards,
Geert