XLOOKUP with Blank Cell in Lookup Value & Blank Cell in Lookup Array resulting in a date rather than a Blank

[GCFC]

I am reconciling multiple data sources and I have a situation where I'm using Lookup Values and some of these values are blank cells and it's comparing to a lookup array which contains both cells with values and cells which are blank. The Return Array contains dates on all rows (so even the blank cells have a date value).

What I need to occur is where the Lookup value is blank the return should be blank, rather than a date.

How do I achieve the desired outcome?

The XLOOKUP Formulae I've used without achieving the desired result are:

=IF(XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128)="","",XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128)) as suggested by XLOOKUP return blank if blank

=IF(ISBLANK(XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128)),"",XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128)) as suggested by XLOOKUP Returns 0? Return Blank Instead - Excel - Automate Excel

=IFERROR(1/(1/XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128,"-")),"") as suggested by XLOOKUP for dates avoid 1/0/1900 for blank cells [SOLVED]

=XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128,"")

=IF(IFNA(XLOOKUP(K10,$K$4:$K$1128,$A$4:$A$1128),0)=0,"",XLOOKUP(K10,$K$4:$K$1128,$A$4:$A$1128))

=LET(x,XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128),IF(x="","",x)) which results in #NAME?

 

[GCFC]

I get 179 different Row references.

Sheet2 Column A	Sheet 2 Column K
23/12/2022​
23/12/2022​	Unique Reference ID
28/11/2022​	Unique Reference ID
23/11/2022​	Unique Reference ID
23/11/2022​	Unique Reference ID
3/11/2022​	Unique Reference ID
27/10/2022​	Unique Reference ID
27/10/2022​	Unique Reference ID
24/10/2022​	Unique Reference ID
20/10/2022​	Unique Reference ID
19/10/2022​	Unique Reference ID
6/10/2022​	Unique Reference ID
29/09/2022​	Unique Reference ID
29/09/2022​	Unique Reference ID
29/09/2022​	Unique Reference ID
29/09/2022​	Unique Reference ID
23/09/2022​	Unique Reference ID
23/09/2022​	Unique Reference ID
19/09/2022​	Unique Reference ID
8/09/2022​	Unique Reference ID
1/09/2022​	Unique Reference ID
30/08/2022​	Unique Reference ID
30/08/2022​	Unique Reference ID
30/08/2022​	Unique Reference ID
24/08/2022​	Unique Reference ID
22/08/2022​	Unique Reference ID
11/08/2022​	Unique Reference ID
4/08/2022​	Unique Reference ID
28/07/2022​	Unique Reference ID
21/07/2022​	Unique Reference ID
18/07/2022​	Unique Reference ID
18/07/2022​	Unique Reference ID
14/07/2022​	Unique Reference ID
8/07/2022​	Unique Reference ID
8/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
1/07/2022​	Unique Reference ID
30/06/2022​	Unique Reference ID
23/06/2022​	Unique Reference ID
16/06/2022​	Unique Reference ID
16/06/2022​	Unique Reference ID
16/06/2022​	Unique Reference ID
9/06/2022​	Unique Reference ID
6/06/2022​	Unique Reference ID
26/05/2022​	Unique Reference ID
24/05/2022​	Unique Reference ID
19/05/2022​	Unique Reference ID
19/05/2022​	Unique Reference ID
18/05/2022​	Unique Reference ID
16/05/2022​	Unique Reference ID
16/05/2022​	Unique Reference ID
12/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
6/05/2022​	Unique Reference ID
5/05/2022​	Unique Reference ID
28/04/2022​	Unique Reference ID
21/04/2022​	Unique Reference ID
21/04/2022​	Unique Reference ID
21/04/2022​	Unique Reference ID
14/04/2022​	Unique Reference ID
7/04/2022​	Unique Reference ID
31/03/2022​	Unique Reference ID
24/03/2022​	Unique Reference ID
18/03/2022​	Unique Reference ID
17/03/2022​	Unique Reference ID
16/03/2022​	Unique Reference ID
16/03/2022​	Unique Reference ID
10/03/2022​	Unique Reference ID
3/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
2/03/2022​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
7/09/2021​	Unique Reference ID
21/06/2021​	Unique Reference ID
21/06/2021​	Unique Reference ID
21/06/2021​	Unique Reference ID
21/06/2021​	Unique Reference ID
21/06/2021​	Unique Reference ID
21/06/2021​	Unique Reference ID
18/06/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
7/04/2021​	Unique Reference ID
23/03/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
23/02/2021​	Unique Reference ID
27/01/2021​	Unique Reference ID
4/01/2021​	Unique Reference ID
24/12/2020​	Unique Reference ID
24/12/2020​	Unique Reference ID
24/12/2020​	Unique Reference ID
24/12/2020​	Unique Reference ID
14/12/2020​	Unique Reference ID
4/12/2020​	Unique Reference ID
30/11/2020​	Unique Reference ID
27/11/2020​	Unique Reference ID
20/11/2020​	Unique Reference ID
20/11/2020​	Unique Reference ID
17/11/2020​	Unique Reference ID
12/11/2020​
12/11/2020​	Unique Reference ID
6/11/2020​	Unique Reference ID
30/10/2020​	Unique Reference ID
26/10/2020​	Unique Reference ID
22/10/2020​	Unique Reference ID
22/10/2020​
23/09/2020​	Unique Reference ID
22/09/2020​
#N/A​

 

[Alex Blakenburg]

In your sample data you do not have any Unique Reference IDs with a blank in column A which is what we think you are referring to.
Show us K9 and show us the line you are expecting it to find and what is in columns K & A of that line.

 

[GCFC]

In your sample data you do not have any Unique Reference IDs with a blank in column A which is what we think you are referring to.
Show us K9 and show us the line you are expecting it to find and what is in columns K & A of that line.

Column K = Lookup value & contains Unique Reference IDs and blank cells
Sheet 2 Column K = Lookup Reference and contains the same Unique Reference IDs as Sheet 1's Column K and also blank cells
Sheet 2 Column A = Various Dates (including duplicated dates).

What I'm looking to achieve is that if the Lookup Value cell is blank & Sheet2 Column K is also blank (which should inevitably be the case) then the returning result should be blank rather than a date or a error. Where there is a Unique Reference ID then I want the date value from Sheet 2 Column A

 

[GCFC]

In your sample data you do not have any Unique Reference IDs with a blank in column A which is what we think you are referring to.
Show us K9 and show us the line you are expecting it to find and what is in columns K & A of that line.

Sheet 1 K9 = blank cell (the first cell in Sheet 1 Column K that has a unique reference ID is K80)

The ROW result I get for K9 is: row 866

Sheet 2 Column K =Blank
Sheet 2 Column A = 22/10/2020 (whereas I want this to be showing as blank, basically if K9 is blank, then result should be blank)

Sheet 1 K10 = blank cell

The ROW result I get for K10 is: row #N/A


Sheet 1 K11 = blank cell

The ROW result I get for K9 is: row 874 (as you can see no consistency in treatment of blank cell in Sheet 1 Column K)

Sheet 2 Column K =Blank
Sheet 2 Column A = 22/09/2020 (whereas I want this to be showing as blank, basically if K9 is blank, then result should be blank)

 

[Alex Blakenburg]

That is not the scenario the formulas you are using are targeting.
Try this.
=IF(K9="","",XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128))
And if you don't want the NAs this.
=IF(K9="","",XLOOKUP(K9,$K$4:$K$1128,$A$4:$A$1128,""))