0

There is a data set with cells containing multiple values. I need to cross check each cell in the dataset with a predetermined list of values and return all matching values in one cell per row.

enter image description here

The best option would be to use TEXTJOIN function but it doesn't work in my Excel 2016. Here is the alternative code I tried to make but it returns only the first value in the array.

{=IF(SUMPRODUCT(--ISNUMBER(SEARCH(list,A2))), IF(ISNUMBER(SEARCH(list,A2)), list, ""), "")}

Will greatly appreciate any help!

Tart
  • 305
  • 1
  • 6
  • 20
  • 2
    With out TEXTJOIN you will need vba – Scott Craner Jul 12 '19 at 16:39
  • 2
    Have a look here for a udf for TEXTJOIN: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell and look here for a TEXTJOINIFS: https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a – Scott Craner Jul 12 '19 at 16:40
  • The only way to do it with formula is if your `list` is finite and you test each one in a separate Search and concatenate each search, In your example you would string together three Search functions, one for each item in the list. This gets unruly with long lists. – Scott Craner Jul 12 '19 at 16:42
  • My list is pretty long - 53 values and it can aslo be increased later.. – Tart Jul 12 '19 at 16:44
  • 5
    Then VBA either as a UDF like I linked to or a sub will be needed. – Scott Craner Jul 12 '19 at 16:45
  • Your TEXTJOIN Formula would be `=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(list,A2)),list,""))` entered as an array formula with Ctrl-Shift-Enter. Put the code from the first link in a module attached to the workbook and use the formula. – Scott Craner Jul 12 '19 at 16:47
  • AWESOME! Thank you so much for your help! – Tart Jul 12 '19 at 16:57

1 Answers1

0

The same has been answered through comments by Scott Craner, hence the same is in unanswered history. Behalf of Scott posting answer here which have accepted by question owner.

Your TEXTJOIN Formula would be =TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(list,A2)),list,"")) entered as an array formula with Ctrl+Shift+Enter. Put the code from the first link in a module attached to the workbook and use the formula

Regiz
  • 459
  • 4
  • 18