Split cell text by delimeter and perform vlookup on each

Question

I have cells that contain text seperated by commas such as:

apples, bananas, mango

The number of items in each cell vary, some have one, some have as many as 10.

I'm looking to loop through each item in the cell and then perform a vlookup on them, I'd quite like the vlookup result to be put into the same format in the resulting cell. So for example if there are matches from multiple items in my original cell, the resulting cell will contain multiple results separated by a comma.

I think ill be fine with the vlookup logic but really struggling to have a sort of "for loop" to go through all items in my list separated by the comma.

Answer 1

Using Formula currently Available in Office 365 and Excel 2019 for PC:

=TEXTJOIN(", ",TRUE,IFERROR(VLOOKUP(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"),F1:G3,2,0),""))

Using Functions that are currently only available to Office Insiders beta channel:

=TEXTJOIN(", ",TRUE,XLOOKUP(TEXTSPLIT(A1,", "),F1:F3,G1:G3,""))