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 type in "Ignore"
  • Else, use the LET function to lookup by Invoice# in the LookUp-Comment worksheet

However, the function in Cell D2 of the SORTED_INV_CN worksheet is returning "Ignore" for all the rows in Column D. At the end of the day, I am required to evaluate the xlookup function for date range less than Dec 2025 and April 2025. I am partial to the LET function as it reduces the need to add a 2nd xlookup formula. The LET function works without the parent IF function but is returning erroneous data as soon as I incorporate the IF(OR( function.

 

Here is the link to the file: 

https://docs.google.com/spreadsheets/d/1oiWWXXOSorZuB5Q4vzgdHO_kyRkvGE3L/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true

 

I think the problem lies in the way the date format is being interpreted between the evaluation cells.  Hope you can point me in the right direction. Thank you.

Regards,

Shams.

excel
Formulas and Functions

2 Replies

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

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 22, 2025

    Do you mean date range to ignore or just those two dates? To ignore two date range try-

    =IF(AND(A2>=$G$3,A2<=$G$2),"Ignore",LET(x,"Your formula here",x))

    To ignore only those two date, try-

    =IF(OR(A2=$G$2,A2=$G$3),"Ignore",LET(x,"Your formula here",x))

     

Resources