Forum Discussion

ShamsM's avatar
ShamsM
Copper Contributor
Dec 19, 2025

Using a combination of IF Statement and LET function

Hello, I am trying to do the following from the attached link: If the Fiscal period date in Col B (of the SORTED_INV_CN worksheet) is greater than/equal to Dec 2025 or less than April 2025 then ty...
excel
Formulas and Functions
Olufemi7's avatar
Olufemi7
Iron Contributor
Dec 23, 2025

Hello ShamsM​, 

It looks like the issue is with how the dates are being evaluated in your IF(OR(...)) logic. If the fiscal period values in column B are stored as text (e.g. "Apr 2025"), the comparison will always evaluate as TRUE and return "Ignore". You’ll want to make sure those are proper date values.

A clean way to structure this with LET is:

=LET(
    fiscalDate, B2,
    invoiceNum, C2,
    cutoffLow, DATE(2025,4,1),
    cutoffHigh, DATE(2025,12,1),
    IF(OR(fiscalDate < cutoffLow, fiscalDate >= cutoffHigh),
        "Ignore",
        XLOOKUP(invoiceNum, 'LookUp-Comment'!A:A, 'LookUp-Comment'!B:B, "Not Found")
    )
)



Key points:

  • Use DATE(2025,4,1) and DATE(2025,12,1) instead of typing "Apr 2025" or "Dec 2025". Text dates are unreliable.
  • Confirm column B contains real dates: =ISNUMBER(B2) should return TRUE. If not, convert with DATEVALUE(B2) or reformat the column.
  • The logic is:
    • "Ignore" if before April 2025
    • "Ignore" if on/after December 2025
    • Otherwise, run the XLOOKUP.

If you test with a helper formula like:

=IF(OR(B2 < DATE(2025,4,1), B2 >= DATE(2025,12,1)), "Ignore", "Keep")


and it still returns "Ignore" for everything, that confirms column B is text rather than dates.

Resources