# IFS function not returning a result

Occasional Visitor

# IFS function not returning a result

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

# Re: IFS function not returning a result

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

# Re: IFS function not returning a result

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:

cheers

# Re: IFS function not returning a result

As variant

``````=IFNA(
LOOKUP( L2,
{14,25,35,45,55,65},
{"15-24 years","25-34 years","35-44 years","45-54 years","55-64 years","65+ years"} ),
"too young")``````