Forum Discussion

Ben Wilson's avatar
Ben Wilson
Brass Contributor
Dec 06, 2018
Solved

How to display many results with using VLOOKUP

Using VLOOKUP (I'm a newbie at it) and was wondering how to be able to display all the results from a contract number and show the invoices associated with it (one to many) in the cell. It's seems to pull the first of the invoices associated with the contract number, but not the others. The lookup is working, just not pulling all the info I'm looking for. My formula is below: My workbook has two tables: (I do have relationships created between the two tables as well) The "Summary" table is where I'm pulling the results into The "Invoice" table is where I'm pulling the list of invoices from. This as well is where the contract numbers are (hence the relationship between the two tables) =VLOOKUP([@[Contract:]],projectinvoicelist!$A$2:$B$71, 2, FALSE) Thanks for any input or other recommendations! -Ben
excel
  • Haytham Amairah's avatar
    Haytham Amairah
    Dec 07, 2018

    Hi Ben,

     

    The VLOOKUP was found to live in the Many side, not in the One.

     

    Here's an example:

     

    In the above example, we have two tables, and the relationship between them is one-to-many, where the one contact may get many invoices.

    In this case, you can use the VLOOKUP in the Invoices table to get the contact name for each invoice.

    This is what VLOOKUP is good at!

     

    VLOOKUP isn't helpful to what you asking for.

    So, you have to find out another approach to achieve what you want like the formula suggested by SergeiBaklan.

     

    You can use it in the example above as follows:

    =TEXTJOIN(", ",TRUE,IF($E$4:$E$13=A4,$F$4:$F$13,""))

     

    But please note that this is an array formula, so you have to press Ctrl+Shift+Enter at the same time to enter it.

    And you need to Excel 2019 or Office 365 installed in your machine to be able to use this formula because https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c is only compatible with these versions.

     

    Hope that helps

4 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 07, 2018

    Hi Ben,

     

    The VLOOKUP was found to live in the Many side, not in the One.

     

    Here's an example:

     

    In the above example, we have two tables, and the relationship between them is one-to-many, where the one contact may get many invoices.

    In this case, you can use the VLOOKUP in the Invoices table to get the contact name for each invoice.

    This is what VLOOKUP is good at!

     

    VLOOKUP isn't helpful to what you asking for.

    So, you have to find out another approach to achieve what you want like the formula suggested by SergeiBaklan.

     

    You can use it in the example above as follows:

    =TEXTJOIN(", ",TRUE,IF($E$4:$E$13=A4,$F$4:$F$13,""))

     

    But please note that this is an array formula, so you have to press Ctrl+Shift+Enter at the same time to enter it.

    And you need to Excel 2019 or Office 365 installed in your machine to be able to use this formula because https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c is only compatible with these versions.

     

    Hope that helps

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 06, 2018

    Hi Ben,

     

    If TEXTJOIN available for your Excel version you may use the formula like

    =TEXTJOIN(",",TRUE,IF(projectinvoicelist!$A$2:$A$71=[@[Contract:]],projectinvoicelist!$B$2:$B$71,""))

    above is array formula

    • Ben Wilson's avatar
      Ben Wilson
      Brass Contributor
      Dec 07, 2018
      Sergei,
      Thank you for your input! However, that didn't bring it in either.

Resources