Fixing a formula in microsoft excel it brings #N/A

Question

Fixing a formula in microsoft excel it brings #N/A

STEPHAN MWASEBA 0

What is this formula missing because it is bringing #N/A

=IFS(@Employee_details[START DATE]=0,"",AND(L26>=K30,L26<=L30),M30,AND(L26>=K31,L26<=L31),M31,AND(L26>=K32,L26<=L32),M32,AND(L26>=K33,L26<=L33),M33,AND(L26>=K34,L26<=L34),M34,AND(L26>=K35,L26<=L35),M35,AND(L26>=K36,L26<=L36),M36,AND(L26>=K37,L26<=L37),M37,AND(L26>=K38,L26<=L38),M38,AND(L26>=K39,L39<=L31),M39,AND(L26>=K40,L26<=L40),M40,AND(L26>=K41,L26<=L41),M41)

Rory Archibald 19,230 Reputation points Volunteer Moderator

2026-10-30T10:56:27.89+00:00

It means that none of your criteria are being matched. By the look of your formula, you have a lookup table of dates, in which case - if there are no overlaps in the dates - you can almost certainly use a simpler lookup formula like =IF(@Employee_details[START DATE]=0,"",xlookup(L26,$K$30:$K$41,$M$30:$M$41,"No match",-1))

1 answer

Your answer

Rory Archibald 19,230 Reputation points Volunteer Moderator

2026-10-30T10:56:27.89+00:00

It means that none of your criteria are being matched. By the look of your formula, you have a lookup table of dates, in which case - if there are no overlaps in the dates - you can almost certainly use a simpler lookup formula like =IF(@Employee_details[START DATE]=0,"",xlookup(L26,$K$30:$K$41,$M$30:$M$41,"No match",-1))

Answer 1

Correct the row-39 test from L39<=L31 to L26<=L39, and add a final TRUE condition so IFS has a result when L26 falls outside every listed range.

=IFS(
 @Employee_details[START DATE]=0,"",
 AND(L26>=K30,L26<=L30),M30,
 AND(L26>=K31,L26<=L31),M31,
 AND(L26>=K32,L26<=L32),M32,
 AND(L26>=K33,L26<=L33),M33,
 AND(L26>=K34,L26<=L34),M34,
 AND(L26>=K35,L26<=L35),M35,
 AND(L26>=K36,L26<=L36),M36,
 AND(L26>=K37,L26<=L37),M37,
 AND(L26>=K38,L26<=L38),M38,
 AND(L26>=K39,L26<=L39),M39,
 AND(L26>=K40,L26<=L40),M40,
 AND(L26>=K41,L26<=L41),M41,
 TRUE,""
)

Two issues were present:

The row-39 condition contains incorrect references Original:
```
   AND(L26>=K39,L39<=L31)
```
Corrected:
```
   AND(L26>=K39,L26<=L39)
```
There was no default result IFS returns #N/A when none of its conditions evaluates to TRUE. A final TRUE,"" returns a blank instead. You can replace the blank with a diagnostic message:
```
   TRUE,"No matching range"
```
Microsoft documents this final TRUE condition as the way to specify a default result for IFS.^1^

If #N/A remains after these corrections, check whether any referenced cell—particularly K30:L41, M30:M41, L26, or the start-date field—already contains an #N/A error. Formulas referring to cells that contain #N/A can propagate that error.^2^

References