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
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 😉
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 02, 2021

    Coupland1925 

    Thank you for the like, but don't care, that's not a problem.

    I still didn't catch, sorry - new columns are in first range or in second one? Perhaps you may manually create small sample file to illustrate the question?

    I checked previous posts, didn't find on which version of Excel you are? Depends on that is 365 or not solutions could be different - not to generate again all possible variants. 

    • mtarler's avatar
      mtarler
      Silver Contributor
      Feb 02, 2021

      SergeiBaklan Coupland1925  OK I was just joking about the likes, but apparently my humor fell flat.  

      As for the formula I totally missed the row 2 vs row 3.  The point of my response however is still true that the easiest solution in this 'simple' case is just to manually fuse those 2 regions:

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

      or you could do

       

      =TEXTJOIN(",",TRUE,IF(AND(J3:U3<>"",MEDIAN(14,COLUMN(J3:U3),15)<>COLUMN(J3:U3)),J2:U2,""))

       

       

       

      • Coupland1925's avatar
        Coupland1925
        Copper Contributor
        Feb 03, 2021
        Thank you both again so much - I think that you've solved it for me 🙂 Again - huge kudos to you both for the help it's been invaluable...
        I have one last question relating back to that very first formula that you solved for me... but I'll create a new question on the board now I know the rules lol 🙂
        Thank you again and again 🙂

Resources