-1

I have look up table:

A B
A Apple
B Banana

The I have column as

A
A
A,B
B

I like the result to show as

A B
A Apple
A,B Apple, Banana
B Banana
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
xiaoyunwu
  • 69
  • 6
  • It is a bit unclear what you are asking. Maybe providing screenshots or tabular data would help. – kojow7 Apr 26 '21 at 22:00
  • If you don't have Office 365, you can do this with Power Query or VBA. Please indicate your Excel version and if you have the `TEXTJOIN` function. – Ron Rosenfeld Apr 27 '21 at 11:28

1 Answers1

1

To do this with a formula one must have TEXTJOIN which is available in Office365 and Excel 2019.

=TEXTJOIN(", ",TRUE,VLOOKUP(FILTERXML("<a><b>"&SUBSTITUTE(D1,",","</b><b>")&"</b></a>","//b"),A:B,2,FALSE))

enter image description here

If one does not have TEXTJOIN, vba or many helper columns will be necessary.

using vba one can create a TEXTJOIN look-a-like. See HERE for one.

We would also need to shift to an INDEX MATCH:

=TEXTJOIN(", ",TRUE,INDEX(B:B,N(IF({1},MATCH(FILTERXML("<a><b>"&SUBSTITUTE(D1,",","</b><b>")&"</b></a>","//b"),A:A,0)))))

And this would need to be confirmed using Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Only have excel 2016, tried to use the TEXTJOIN function, however, still don't know how to get the final result, get error in value – xiaoyunwu Apr 27 '21 at 16:09
  • Thank you so much, Scott, it worked. Could you please explain a bit about N, IF({1}...) part? when I only use FilterXML on A,B itself, it only returns A, so how did it returned an array? – xiaoyunwu Apr 27 '21 at 22:39
  • The filterxml returns an array, but if you only put it in one cell it will only return the first. A cell cannot hold an array. the `N(IF({1}...))` is a little loop hole to force INDEX to accept the array of numbers returned. In older versions INDEX will not naturally accept arrays. – Scott Craner Apr 27 '21 at 22:41
  • Also, please consider marking as correct by clicking the check mark by the answer. – Scott Craner Apr 27 '21 at 22:42