3

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Ed Jones
  • 321
  • 1
  • 2
  • 11
  • 1
    What version of Excel do you have? – BigBen Apr 28 '22 at 17:53
  • To have the result as a comma delimited list it will require TEXTJOIN() available in Office 365 Excel and Excel 2019 or later or it will require vba. – Scott Craner Apr 28 '22 at 17:54
  • Yes I have 2019. Actually I am currently doing a textjoin on the source cell as the comma seperated values in here are derived from a previous vlookup! – Ed Jones Apr 28 '22 at 18:00

1 Answers1

3

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),""))

enter image description here

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

=TEXTJOIN(", ",TRUE,XLOOKUP(TEXTSPLIT(A1,", "),F1:F3,G1:G3,""))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Wow, thanks so much for such a quick reply and a working formula, just what I needed! – Ed Jones Apr 28 '22 at 18:04
  • Sir, will `FILTERXML()` spill the values since OP is using Excel 2019? – Mayukh Bhattacharya Apr 28 '22 at 18:08
  • @MayukhBhattacharya no. Office 2019 came out before dynamic array formula. – Scott Craner Apr 28 '22 at 18:10
  • Then sir, how will this work, ? Since `FILTERXML()` shall give only apples, and not the rest. – Mayukh Bhattacharya Apr 28 '22 at 18:10
  • 1
    @MayukhBhattacharya it is not spilling it, but that does not mean the array does not exist. It returns an array to the TEXTJOIN. But in 2019 one would need to use INDEX or array enter the formula over many cells to show the entire array. Array formula do not require Spill to work. We have been using array formula long before Office 365 introduced the Spill. – Scott Craner Apr 28 '22 at 18:12
  • Sir, I am asking because same kind of formula i gave someone but it was not working, one moment let me show you the link. – Mayukh Bhattacharya Apr 28 '22 at 18:13
  • 1
    @MayukhBhattacharya if they do not have Office 365 then they will need to use Ctrl-Shift-Enter to Array enter the formula – Scott Craner Apr 28 '22 at 18:14
  • Alright Sir, thank you, understood now. – Mayukh Bhattacharya Apr 28 '22 at 18:15
  • @ScottCraner Sir, see this query, I forgot, I have not used `FILTERXML()` but it was like using within `VLOOKUP()` Function only, but it wasnt working, could you give some advice here [thisone](https://stackoverflow.com/questions/71655330/assigned-a-value-to-characters-and-perform-a-mathematical-functions-of) – Mayukh Bhattacharya Apr 28 '22 at 18:20