1

I have two tables, which look like this:

enter image description here

I would like Excel to use the value in ID company associated in table 2 (green), look for it in table 1 (orange) and return the value from Company name, or multiple values when multiple companies are associated with the same person in table 2.

What would be the best solution?

5

Here's a formula that will work in Excel 2016, as is. In earlier versions of Excel, a poly-fill UDF for TEXTJOIN() is required. (See this post for a basic one.)


Worksheet Screenshot

Array enter (Ctrl+Shift+Enter) the following formula in F2 and copy-paste/fill-down into the rest of the column:

{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}

Note that this formula only works if the values in column A are actually stored as numbers. For text values, the --MID(…) in the formula needs to be replaced by TRIM(MID(…)).


The prettified formula is as follows:

{=
  TEXTJOIN(
  ";",
  TRUE,
  INDEX(
    (B:B),
    N(IF(1,
      MATCH(
        --MID(
          SUBSTITUTE(E2,";",REPT(" ",99)),
          99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
          +(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
          99
        ),
        (A:A),
        0
      )
    ))
  )
)}

Notes:

  • The prettified formula actually works if entered.
  • The brackets around (A:A) in the prettified version are required to force the A:A to remain on its own line. The same applies for the (B:B).


For Excel 2016 (Windows only) the following simpler formula should work:

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

Just like the previous formula, this one also only works on values stored as numbers. For text values, just remove the -- from the formula.

  • Hi RobinCTS, thanks for the answer provided, it works tremendously. A last question related to the above: how do I have the formula to add a separator between names, say a semicolon between company name? Much appreciated. – Lucio Jun 15 '18 at 13:36
  • @Lucio Umm, it already does that. That's what the first ; in the formula is for. You can also change it to any other separator string. PS If you really appreciated my help, once you reach 15+ reputation don't forget to come back and upvote my answer as well ;-) – robinCTS Jun 15 '18 at 13:51
  • At a first look, it seems like I'm getting only 1 value for every cell in column F (1 company name per cell in column F). I was expecting at least a few cells with multiple values separated by a semicolon. It is possible, but very unlikely given the data set I'm working with. I will investigate thoroughly the file and get back to you. PS: Will do. – Lucio Jun 15 '18 at 14:07
  • @Lucio You need to array-enter the formula using Ctrl+Shift+Enter, instead of just Enter. (That's why the braces are there - to remind you to do so ;-) ) PS It isn't the cause of your issue, but for future reference I have added information to the answer concerning numbers stored as text versus numbers stored as actual numbers PPS I've also slightly simplified the non-prettified first formula. (The prettified version was already the simpler version.) – robinCTS Jun 15 '18 at 14:43
  • Worked 100%. Thank you for the clarification. – Lucio Jun 15 '18 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.