Forum Discussion

Bookhimdanno's avatar
Bookhimdanno
Copper Contributor
Jul 01, 2022

vlookup two references from a single cell....

Hi,   I have tried searching for this query on forums, but not finding what I require.   Basically, I would like to be able to vlookup two references within a single cell separated by a comma/sla...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jul 02, 2022

Bookhimdanno 

=VLOOKUP(B9,CHOOSE({1,2},F5:F12&","&G5:G12,H5:H12&","&I5:I12),2,FALSE)

Is this what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

Bookhimdanno's avatar
Bookhimdanno
Copper Contributor
Jul 04, 2022

OliverScheurich The formula does work as you intended, but unfortunately this is not what I need. Maybe the below image will give you a better idea. The 1st result shown is what I am looking for, with the ability to add further letters in up to a maximum of 5 characters, which is shown in result 2 = 3 characters - 3 different Bob's 

Thanks for the help

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 04, 2022

    Bookhimdanno 

    In B3:

     

    =TEXTJOIN(",",TRUE,FILTER($E$2:$E$10,ISNUMBER(SEARCH($D$2:$D$10,B2))))

     

    In B7:

     

    =TEXTJOIN(",",TRUE,FILTER($E$2:$E$10,ISNUMBER(SEARCH($D$2:$D$10,B6))))

    • Bookhimdanno's avatar
      Bookhimdanno
      Copper Contributor
      Jul 05, 2022

      HansVogelaar thanks for the reply. I realised quite quickly that excel 2016 does not support the textjoin function, so I managed to find a vba work around, but then also realised that it does not support the filter function either. Frustrating. 

       

      I am not sure if you can offer a workaround to this problem?

       

      I dont have much choice in regards to the version of excel I use, because it is on our businesses systems.

       

      Thanks

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 05, 2022

        Bookhimdanno 

        If you know how to join the text with with vba, you may use

        =INDEX( E2:E10, MATCH( FILTERXML("<t><s>" & SUBSTITUTE(B2, ",", "</s><s>") &"</s></t>", "//s"), D2:D10, 0) )

        and textjoin the result.

        FILTERXML splits content of B2 on elements, MATCH finds their positions and INDEX returns related names. Perhaps you need Ctrl+Shift+Enter, depends on how vba textjoin works.

Resources