Jan 27 2021 05:18 AM
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
Jan 27 2021 05:34 AM - edited Jan 27 2021 05:45 AM
@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)
Jan 27 2021 08:50 AM
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))
Jan 27 2021 09:15 AM
@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!!
Jan 27 2021 09:16 AM
Jan 27 2021 09:30 AM
@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.
Feb 02 2021 05:55 AM
Feb 02 2021 06:30 AM
@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.
Feb 02 2021 06:54 AM
@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?
Feb 02 2021 06:58 AM
Feb 02 2021 07:02 AM
Feb 02 2021 07:08 AM
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.
Feb 02 2021 07:22 AM
@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,""))
Feb 02 2021 08:11 AM
@mtarler yes, if we work with J:M and P:U ranges separately, but I'm still not sure
Feb 03 2021 01:37 AM
Feb 03 2021 05:36 AM
@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.