mapping vlookup for each element of splitted list in google sheets

Question

This question is also answered here: Get a vlookup of a cell after split in Google sheet

but not marked as corrected answer, and cannot make it work.

Goal : I want to apply a vlookup function to a split function, so that I can search for corresponding values (found by the vlookup) for each token obtained from a string.

Consider this sheets:

// Sheet 'veggies'

A
apple, pine, tree
pine

// Sheet 'themes':

A	B	C
apple	8	theme1,theme2
tree	3	theme2
pine	1	theme1,theme3

I want to:

1. split cells of column A of 'veggies' by commas, so to have tokens
2. vlookup for the C column in 'themes' sheet, by using the index of tokens, for all of them

As approach I tried to first retrieve the frequences of tokens in column B, sheet 'themes', and cannot understand what my formula is doing:

=ARRAYFORMULA( VLOOKUP( split(A2;",");'themes'!A$2:D;2;FALSE))

This formula only get the frequency from column be for the first token, while for others will only report N/A saying could not find a value, but it is clearly there.

Any help? Am I on the right track ?

P.s. if one would like to offer use of query , like in the other SO answer, please help me to break down what it does.

Answer 1

ARRAYFORMULA( VLOOKUP( split(A2;",");'themes'!A$2:D;2;FALSE))

Your formula works. But when splitting by comma , there's a extra space left over in all the elements from the second element. So, when apple, pine, tree is splitted, it becomes apple, pine, tree(note the extra space prefix). To fix, you can simply add a space to the split as well:

=ARRAYFORMULA( VLOOKUP( split(A2;", ");'themes'!A$2:D;2;FALSE))