If function and Xlookup-CodePudding

I am currently trying to run an excel function that runs the logic that if the cell that has date X is greater than date 2023-01-01 then run an XLookup function. If it is not then do not run it.

Here's what I tried below:

=IF(table2!N5>DATE(2023,1,1),XLOOKUP(A2,'Table2'!A:A,'Table2'!B:B),"FALSE")

N5 has the date 2022-12-01. The rest is just a standard xlookup function that would run if cell N5 is greater than the date 2023-01-01.

Data for the Xlookup below:

Table 1 : This table provides the lookup value A1.

       A             B              C
 1  Position    Classification  Full Name
 2  111111       IT   04        John Smith

Table 2 : This table provides the lookup array being A:A and the return array being B:B

        A                   B              C                D
1 Employment Position       CC         Valid From        Valid To
2      111111             8888888      2022-10-01       2023-03-31

CodePudding user response：

I would advise you to use the DATE() function, like this:

= IF(A1>DATE(2023,1,1), ...)

CodePudding user response：

As posted in comments, I will suggest you use a cell reference, so it becomes more dynamic to use, instead of hard coding within a formula,

• instead of this

=IF(N5>0 "2023-01-01",XLOOKUP(E3,H:H,J:J),"FALSE"))

• Use a cell reference, let's say cell A1 = 2023-01-01

=IF(N5>A1,XLOOKUP(E3,H:H,J:J),"FALSE"))

So the above helps you in changing the cell A1 values whenever you need instead of bothering the formula each time for a change.