Forum Discussion
IF Statement
One more variant
=INDEX($Q$1:$CD$1,1,AGGREGATE(15,6,1/(Q3:CD3<>"")*(COLUMN($Q$1:$CD$1)-COLUMN($Q$1)+1),1))- Thank you - this is a completely different formula to the other that works and yet, this also works a dream.
I don't suppose you could explain the Aggregate section could you - I'm wondering what 15,6,1 are looking at?- Hello again,
As you were so super helpful last time, I wondered if you might help with another formula query that I have please?
I am trying amend the following statement so that it can 'skip' some columns where I don't wish to include in the result.
This is my formula - that worked a dream until someone wanted to add some additional columns in the worksheet...
=TEXTJOIN(",",TRUE,IF(J3:U3<>"",J2:U2,""))
Now what I'd like to do is... for example look up column j to M and then pick back up again from columns p to U.
Is this possible? I had thought it would be as easy as =TEXTJOIN(",",TRUE,IF(J3:M3,P3:U3<>"",J2:U2,"")) But this doesn't work...Coupland1925 I'm surprised SergeiBaklan didn't reply already but maybe he is in protest because you didn't give him any 'likes' or 'best answer'. and in general this should be considered a new question so you could/should open a new post.
That said I don't understand why you have that IF() statement in that formula. There are other 'fancier' options I see, but see if this gives the result you want:
=TEXTJOIN(",",TRUE,J3:M3) & ", " & TEXTJOIN(",",TRUE,P3:U3)If there were a lot of regions, especially with some sort of pattern, you could use IF() or FILTER() to help.
Coupland1925 glad to help.
Also w/r to SergeiBaklan formula, AGGREGATE is a function that has multiple functionalities built in. In particular the "15" tells the function to find the xth smallest in the array and the last parameter "1" says to find the 1st smallest. The "6" is a parameter to select options and in this case the "6" is to ignore the data points that are errors. So basically the formula part intentionally causes error (1/0 which is infinity and hence and error) for the values you want to exclude from the list and the other values to return the column # they are located. Then once those items are excluded from the list the "15" tells it to get the smallest value and hence the 1st column that wasn't excluded. Finally the INDEX then looks up that column in the original list.
Hope that helps but let us know if you still have questions.
