Using Textjoin with IF(AND functionality

**MrRAMMounts** · 10-28-2019, 12:53 PM

Hi

I am trying to figure out how to use the Textjoin function, but with an IF(AND as part of the logical argument.

2019-10-28 16_39_57-Window.png

Above is a small extract of my data set. It is a set of companies with the list of components of ours that they sell (not sensitive info).

In another worksheet I have all the company names and their category (dealer, distributor, integrator etc) in 2 adjacent columns.

I am looking to create a table where if someone types in a product of ours (e.g. RAM-B-101) and types a category into a cell (e.g. Dealer) it will text join all the companies that are the selected category and have the selected product in their range.

=TEXTJOIN(", ",TRUE,IF(AllProducts!A2:AD550=Sheet2!A4,AllProducts!A1:AD1,""))

This works successfully to Textjoin the company names that have the selected product in their range.

However I cannot figure out how to insert an AND function to include matching the company category also.

=TEXTJOIN(", ",TRUE,IF(AND(AllProducts!A2:AD550=$A$4,INDEX(Categories!A2:A31,MATCH(C2,Categories!A2:B31,0))),AllProducts!A1:AD1,""))

This is what I have got to, but it doesn't work and I am hoping one of you guys could give me some advice.

Thanks in advance.

Jon

-edit

I have a duplicate question currently also posted in an additional Forum. Here is the link

https://www.mrexcel.com/forum/excel-...ml#post5363438

**PaulM100** · 10-28-2019, 12:56 PM

=TEXTJOIN(", ",TRUE,IF(AND(AllProducts!A2:AD550=$A$4,INDEX(Categories!A2:A31,MATCH(C2,Categories!A2:B31,0))),AllProducts!A1:AD1,"")).

The red part needs to equal something, otherwise it becomes and(true,false) and equals false instead of true

**MrRAMMounts** · 10-28-2019, 12:59 PM

Paul

Thanks for the quick response. I'm trying to return a TRUE if the cell with the chosen category in it (C2) is matched against the 2 column array with company,category. What do i need to add to the index match section to make it make sense?

Thanks again.

**PaulM100** · 10-29-2019, 04:49 AM

The only thing I can think about would be :
=TEXTJOIN(", ",TRUE,IF(AND(AllProducts!A2:AD550=$A$4,NOT(ISERROR(INDEX(Categories!A2:A31,MATCH(C2,Categories!A2:B31,0))))),AllProducts!A1:AD1,""))

**MrRAMMounts** · 10-29-2019, 05:26 AM

The simplest way I can explain it is like this -

I have two data sets.

2019-10-29 09_23_45-Window.png

I am looking for a formula that allows someone to choose a product and a category and they are given a text joined result of each company that has both selected options.

I currently have all of the companies and products in a worksheet and the companies and their categories in another.

I appreciate any advice.

Thanks

**PaulM100** · 10-29-2019, 05:33 AM

To quote another member of this forum, a workbook sample file equals 1000 pictures, therefore upload a file. Read the yellow banner at the top on how to do it. Mock up the results manually if necessary

**MrRAMMounts** · 10-29-2019, 05:38 AM

Here is an example work book. Please let me know if you are able to use the example data I have given or if you need more.

**AliGW** · 10-29-2019, 05:42 AM

Where in the sample file are the mocked up results? How do the categories fit in?

**MrRAMMounts** · 10-29-2019, 05:47 AM

Hi Ali

Sorry. I'm not very good at this. I have tried to mock up the results to make my intent clearer.

Thanks again.

**MrRAMMounts** · 10-29-2019, 07:10 AM

In the attached example the customer wants to know which Companies they can contact that have the product they need and are a certain category.

They have selected product 5 and they need the resulting companies to be category 2.

As you can see from my example this will ideally return a textjoined value of 'Company B, Company D' as they both meet both criteria.

I can't seem to get the IF(AND to work within the text join.

I can't seem to insert the AND argument as part of the IF correctly. It just returns nothing.

I would appreciate any suggestions.

Thanks

**AliGW** · 10-29-2019, 07:17 AM

Yes, thanks - somebody will take a look for you. Please be patient.

**PaulM100** · 10-29-2019, 07:34 AM

Here is an alternative:

Formula:

Please Login or Register  to view this content.

**MrRAMMounts** · 10-29-2019, 07:58 AM

Hi Paul

I have used the formula that you suggested above and it WORKS! I honestly have no idea why it works but i'm thankful for the suggestion

Thanks a lot

Jon

**MrRAMMounts** · 10-29-2019, 08:21 AM

Originally Posted by PaulM100

Here is an alternative:

Formula:

Please Login or Register  to view this content.

Paul, the only other questions I have. Is there a way to make it so if nothing is in K14 that it won't return any value in K17.

I want it to be blank unless both options are criteria have values.

Thanks

**PaulM100** · 10-29-2019, 09:04 AM

Either =IF(K14="","",=INDEX($B$5:$E$5,SUMPRODUCT(MAX(($B$6:$E$9=$K$14)*(COLUMN($B$6:$E$9))))-COLUMN($B$5)+1)&","&INDEX($G$2:$G$5,MATCH($K$15,$H$2:$H$5,0)))
or

=IF(AND(K14="",K15=""),"",=INDEX($B$5:$E$5,SUMPRODUCT(MAX(($B$6:$E$9=$K$14)*(COLUMN($B$6:$E$9))))-COLUMN($B$5)+1)&","&INDEX($G$2:$G$5,MATCH($K$15,$H$2:$H$5,0)))

**MrRAMMounts** · 10-29-2019, 09:22 AM

Hi

I have made an adjustment to the formula to use a Textjoin for the first section. This works simply for the first section. Is there anyway to use this method but somehow insert an additional criteria to the text join for the company/categories?

Thanks

**MrRAMMounts** · 10-29-2019, 09:30 AM

Hi

Apologies I think the file above was without the formula. I have re attached it.

Regards

**AliGW** · 10-29-2019, 01:32 PM

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

**MrRAMMounts** · 10-30-2019, 05:28 AM

Hi Ali

Do you mean my other posted thread called - 'Re: Reorganising data from A>1,2,3 to 1>A,D,E.'?

If you mean that one then the solved result was the TEXTJOIN formula that I have for this one that allows me to do the first half of the calculation.

I was hoping for some assistance at ways to expand its function to include an additional logical argument. I just don't seem to be able to get the logic correct.

I have attached my example workbook. It includes two formula.

The first was suggested by someone and uses MMULT (I don't understand how it works) to somehow calculate correctly what I need to do. The only caveat is that this
only works with a 4x4 array for this example data. I don't know how to scale up this (or if it can be scaled up) to accommodate 30 columns with between 5 and 550 rows of products in each.

The second is the TEXTJOIN function. This currently works in all cases, but only for 1 search criteria. This works if someone gives the product. It successfully TEXTJOINs all the relevant
companies that contain that product.

My goal is to use one of these formula to add and additional 'AND' into the argument.

IF the searched array returns a match for Criteria 1 (product) AND Criteria 2 (category) then return a TEXTJOINed list.

Is that something that could be done? I don't know the second AND would need to use an INDEX/MATCH function or something else.

If you are certain it can't be done with the data organised like this I am happy to re organise it in a different suggested way.

Thanks for your time.

**AliGW** · 10-30-2019, 06:05 AM

Thanks for the link. Be aware that Mr Excel has the same rule, so you need to post a link back to this thread over there.

**MrRAMMounts** · 10-30-2019, 06:06 AM

Thanks for the heads up, I'll do that now.

**MrRAMMounts** · 10-30-2019, 06:55 AM

Hi

I think it is working now. I need to test it multiple ways but it seems to be working.

Thanks to Eric from Mr Excel who suggested it. Apparently, just having two of the same logical argument for the different arrays in parenthesis and multiplied returns the correct values.

Here is a link showing it working - https://docs.google.com/spreadsheets...it?usp=sharing

Thanks for the help.

Regards

Jonathan

**AliGW** · 10-30-2019, 06:58 AM

Please post the suggested formula and use the attachment facility to attach the sample file here - some members will not follow external links.

When you have done this, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**MrRAMMounts** · 10-30-2019, 07:21 AM

Here is an attachment showing the solved formula.

Thanks for the help.

**AliGW** · 10-30-2019, 07:23 AM

I asked you to post the formula. Here it is for the benefit of others:

=TEXTJOIN(", ",TRUE,IF((B6:E9=K14)*(B13:E13=K15),B5:E5,""))

Thanks for the workbook.

Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Re: Using Textjoin with IF(AND functionality

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] TEXTJOIN and keep formatting

[SOLVED] Textjoin + condition

[SOLVED] TEXTJOIN with multiple criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions