-1

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

enter image description here

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))
sos12
  • 699
  • 1
  • 6
  • 10

2 Answers2

2

You can try with this:

=IF(ISNUMBER(D4),vlookup(D4,A:B,2,0),join(",",query(A:B,"Select B where A = "&JOIN(" OR A = ",split(D4,",",1,1)))))

If D4 is a number (so it's not varios values) it does the VLOOKUP, if it is not it split the values and inserts them in a query, then join the results with commas again.

If you want it for the whole column:

=BYROW(D2:D,LAMBDA(each,IF(each="","",IF(isnumber(each),vlookup(each,A:B,2,0),join(",",query(A:B,"Select B where A = "&JOIN(" OR A = ",split(each,",",1,1))))))))
Martín
  • 7,849
  • 2
  • 3
  • 13
1

Here is an alternative solution using XLOOKUP function:

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

Result: enter image description here

Twilight
  • 1,399
  • 2
  • 11