Dec 06 2018 03:07 PM
Dec 06 2018 03:47 PM
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
Dec 07 2018 03:10 AM
Dec 07 2018 05:16 AM - edited Dec 07 2018 05:29 AM
SolutionHi 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
Dec 07 2018 10:38 AM
Dec 07 2018 05:16 AM - edited Dec 07 2018 05:29 AM
SolutionHi 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