Forum Discussion

Coupland1925's avatar
Coupland1925
Copper Contributor
Jan 27, 2021

IF Statement

Hello, I have a question about a monster IF statement.  My question is - where I've now managed to condense the IF statement by looking at ranges... how do I ask excel to only return the first resul...
excel
Coupland1925's avatar
Coupland1925
Copper Contributor
Jan 27, 2021
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?
Coupland1925's avatar
Coupland1925
Copper Contributor
Feb 02, 2021
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...
  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 02, 2021

    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's avatar
      Coupland1925
      Copper Contributor
      Feb 02, 2021
      Oh gosh, I hope I haven't offended anyone. I'm really new to asking questions on these forums, I have gotten rather giddy with the realisation that there are people out there that know how to write formula so perfectly!!

      Thank you so so much for your answer and sorry - I should have created a new question. I'll remember that for next time. Thank you again and again 🙂
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 02, 2021

      mtarler Nope, I don't care about likes and best answers, just have another things to do and check MTC once per couple of hours if not more seldom.

      Back to request

      =TEXTJOIN(",",TRUE,IF(J3:M3,P3:U3<>"",J2:U2,"")) 

      I'm not sure what is the logic of the request. We check if in row 3 J3:M3 are TRUE AND related value in P3:U3 is not empty, we take the correspondent value from row 2, correct? But from J to M there are 4 columns and from P to U - six. Or we have to check ALL J3:M3 are TRUE and ALL P3:U3 are not empty?

      • Coupland1925's avatar
        Coupland1925
        Copper Contributor
        Feb 02, 2021
        Thank you Sergei,
        Yes that's correct - So I'm looking to see if someone has entered data in row 3 and it's returning a date from row 2 (it's a rather over complicated s/sheet that just keeps growing).
        The issue I have is that someone has come along now and added some new columns with some running totals so my look up from row 3 needs to discount those cells that are right bang in the middle of the range.
        And thank you again for the replies - I've given a like for this one 😉

Resources