How to display many results with using VLOOKUP

[Ben Wilson]

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

[#2]

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

[#3]

Sergei,
Thank you for your input! However, that didn't bring it in either.

[#4 · Accepted solution]

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 @Sergei Baklan.

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 TEXTJOIN is only compatible with these versions.

Hope that helps

[#5]

@Sergei Baklan and @Haytham Amairah, Thank you gentlemen! That did the trick. Once I got the syntax right, it populated the fields perfectly! Thanks again!! Ben