vlookup for each item in comma-separated list

Question

I have two columns of data — id's and data values. I can get vlookup to work if I want to return the correct fruit if I provide an id, but I need to also be able to account for cases where multiple values are provided as well.

Put another way, I need to be able to run my vlookup on each item in a comma-separated list in another cell.

id	fruit		input	output
2835	apples		4792	pears
2232	bananas		2835	apples
3244	peaches		1199,3244,2835,4790	should be: oranges,peaches,etc…
4792	pears
1199	oranges

Lookup multiple values in a single cell (comma separated) and then return the values to a single cell (also comma separated)

Vlookup using a comma separated search key in Google Sheets

I feel like I'm very close with the linked posts above, but I keep getting errors. This is what I have, though I'm open to an alternative approach (or something using Google Apps Script)

=arrayformula(left(concatenate(vlookup(split(D4,","),$A$2:$B$6,2,false)&","),len(concatenate(vlookup(split(D4,","),$A$2:$B$6,2,false)&","))-1))

Answer 1

Here is an alternative solution using XLOOKUP function:

=JOIN(",",ARRAYFORMULA(XLOOKUP(SPLIT(D4,","),A:A,B:B,"",0,1)))

Result: