IFS function not returning a result

Occasional Visitor

I am an Office 365 subscriber and I am using Excel version 2109 build 14430.20298. 


I am new to the IFS function but it has worked in the same spreadsheet in other formulas.  But in this one, I have been unable to get it to return a result.


Column L contains ages in years, formatted as numbers.  In column M, I want to create age groups.  The IFS formula I have used is:


=IFS(L2>14<25,"15-24 years",L2>24<35,"25-34 years",L2>34<45,"35-44 years",L2>44<55,"45-54 years",L2>54<65,"55-64 years",L2>64,"65+ years")


Initially, this formula returned an "#N/A" result.  Then out of the blue for no apparent reason, it just stopped working at all and the formula appears in the cell as text.  I have changed the format of column M to text, back to number, to general and none of it makes a difference.  Calculation options are set to Automatic, the worksheet is not protected. 


Can anyone help with this, please?


3 Replies


=IFS(AND(L2>14,L2<25),"15-24 years",

AND(L2>24,L2<35),"25-34 years",

AND(L2>34,L2<45),"35-44 years",

AND(L2>44,L2<55),"45-54 years",

AND(L2>54,L2<65),"55-64 years",

L2>64,"65+ years")


This formula returns the values for the different ages.



as a variant, you can create an Age Group Table:


and use this formula:

=IFERROR(INDEX($O$2:$O$7,MATCH(L2,VALUE(LEFT($O$2:$O$7,2)),1),0),"Under 15")

and even if you change the Age Group table, the above formula will dynamically adapt to the table.


I added 09-14 in the table but didn't have to change the formula:






As variant

    {"15-24 years","25-34 years","35-44 years","45-54 years","55-64 years","65+ years"} ),
 "too young")