Forum Discussion

GEM012178's avatar
GEM012178
Copper Contributor
Feb 21, 2020
Solved

I need some help with a help vlookup. I need to return several matches into the same cell

how can I get several results from a vlookup into one cell? NOt only the first one.  
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 21, 2020

    GEM012178 

    When it shall be very small icon at the bottom left of the Reply

GEM012178's avatar
GEM012178
Copper Contributor
Feb 21, 2020
I do not have that in my screen =(
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 21, 2020

GEM012178 

When it shall be very small icon at the bottom left of the Reply

  • GEM012178's avatar
    GEM012178
    Copper Contributor
    Feb 24, 2020

    SergeiBaklan 

     

    I tried both formulas and neither works.

    I do not know if itis the file or what is it?

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 21, 2020

    GEM012178 

    Your formula is

    =TEXTJOIN("; ",TRUE,IF(@'C:\Users\marthaelias\Documents\PLANNING\mapeo proceso\[prueba shortage file automatico.xlsx]summary'!D:D=Sheet5!D4,'C:\Users\marthaelias\Documents\PLANNING\mapeo proceso\[prueba shortage file automatico.xlsx]summary'!P:P,""))

     

    You shall remove from it @ character - with it formula works not with entire column, but only with the cell in current row in this column. Plus you don't need file name here. Thus it shall be

    =TEXTJOIN("; ",TRUE,IF(summary!D:D=Sheet5!D5,summary!P:P,""))

    At the same time performance is quite bas since we work here with entire columns. Better to use dynamic ranges as

    =TEXTJOIN("; ",TRUE,IF(summary!D$2:INDEX(summary!D:D,COUNTA(summary!D:D))=Sheet5!D6,summary!P$2:INDEX(summary!P:P,COUNTA(summary!D:D)),""))

    All 3 variants are in attached file.