IF Statement

Copper Contributor

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 result.

 

At the moment the formula is returning all results and will require about 60 columns and I only need the first.

 

Here is my first take on the formula - this returns the result perfectly but is hard to manage =IF(Q5>0,$Q$1,IF(R5>0,$R$1,IF(S5>0,$S$1,IF(T5>0,$T$1,IF(U5>0,$U$1,IF(V5>0,$V$1,IF(W5>0,$W$1,IF($X5>0,$X$1,IF($Y5>0,$Y$1,IF($Z5>0,$Z$1,IF($AA5>0,$AA$1,

 

Here's what I condensed that to... but this brings back all results and I just want the first result

=IF(Q3:CD3<>"",$Q$1:$CD$1,"")

 

If anyone could help that would be great

 

15 Replies

@Coupland1925  I think a LOOKUP approach would be easier:

 

 

 

=XLOOKUP(0,Q3:CD3,Q1:CD1,"",1,1)

 

 

 

EDIT: I assumed this based on your FIRST equation that used >0

and to answer your actual question you could use INDEX to return the first item:

 

 

 

=INDEX(IF(Q3:CD3<>"",Q1:CD1,""),1)

 

 

edit 2: it just occurred to me that your IF statement there will return "" and hence if the first item is "" it will return "" (i.e. I don't think that will do what you want)

maybe use this:

 

 

=INDEX(FILTER(Q1:CD1,Q3:CD3<>""),1)

 

 

 

 

 

 

 

@Coupland1925 

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))

@mtarler Thank you so much - the level of skill here is amazing - first time I have used this board and I'm blown away.

 

Edit 2 does exactly what I need!  Thank you so much!!

 

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  glad to help.

Also w/r to @Sergei Baklan 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.

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 @Sergei Baklan 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.

@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?

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 :)
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 ;)

@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. 

@Sergei Baklan @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,"")) 

 

 

 

@mtarler yes, if we work with J:M and P:U ranges separately, but I'm still not sure

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 :)

@Coupland1925  You're very welcome and if the follow up question is about the first formula maybe it should be here.  I wouldn't call it "rules" as much as organization and best chance for you to get a prompt answer. 

organization: If someone searches for a problem and read the thread do they find their answer or get confused because the thread talks about other different problems... 

prompt answer: It is good practice for a new problem to have new thread because it will get more exposure. Each of us here have our own areas that we are better at so although your reply may 'ping' the members that are already on your thread, if we aren't the best suited or available to answer (although there is little Sergei Baklan doesn't know) then your new question may get lost in the sea (so to say). 

That said if the question is about the original problem/solution then it might be best to be in the original thread for much the same reasons.