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

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

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

IF Statement | Microsoft Community Hub

15 Replies

SergeiBaklan
Diamond Contributor
Jan 27, 2021
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))
- 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
    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
Silver Contributor
Jan 27, 2021
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
  Copper Contributor
  Jan 27, 2021
  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!!

Forum Discussion

IF Statement

15 Replies

Resources