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?

 

[Alex Blakenburg]

Your formula doesn't seem to make sense.
You are looking for the value in K9 in a range data covers K9 being $K$4:$K$1128.
It is always going find the value in K9.

What are you trying to lookup ?

 

[GCFC]

When I was depersonalising the formula I culled the sheet names.

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

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

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

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

=IF(IFNA(XLOOKUP(K9,'SHEET2'!$K$4:$K$1128,'SHEET2'!$A$4:$A$1128),0)=0,"",XLOOKUP(K9,'SHEET2'!$K$4:$K$1128,'SHEET2'!$A$4:$A$1128))

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

 

[Alex Blakenburg]

I thought that was likely.
Since the 1st one works for me I suspect you have a data issue.
Can you try this formula and then go and have a look at the row it returns and see what is in Columns K & A in Sheet2.

=ROW(XLOOKUP(K9,Sheet2!$K$4:$K$1128,Sheet2!$A$4:$A$1128))

 

[GCFC]

I thought that was likely.
Since the 1st one works for me I suspect you have a data issue.
Can you try this formula and then go and have a look at the row it returns and see what is in Columns K & A in Sheet2.

=ROW(XLOOKUP(K9,Sheet2!$K$4:$K$1128,Sheet2!$A$4:$A$1128))

Using your formula results I get a whole bunch of unusual dates when using the General Number format, so converting that to numbers gives row values.

With Respect to the Row Values are these #s in respect to the sheet's row # or the ranges row # (i.e. where a value is returned of 5 is that A5 & K5 or A9 & K9)?

 

[Alex Blakenburg]

They are the sheet row, so 10 is row 10 of the sheet.
XLookup like Index returns not just the value in the cell but the actual cell, so you can use it inside other functions.
eg =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10)) > Example 6 > XLOOKUP function - Microsoft Support

 

[Fluff]

This formula should work if you are using 365

=LET(x,XLOOKUP(K9,'SHEET2'!$K$4:$K$1128,'SHEET2'!$A$4:$A$1128),IF(x="","",x))

Unless you have a very out-of date version of 365. Do you have the LET function?

 

[GCFC]

This formula should work if you are using 365

Unless you have a very out-of date version of 365. Do you have the LET function?

I don't seem to have the LET Function, however having tried to update Excel it says the latest version of office is installed.

Excel Version 2002 (Build 12527.22286)

 

[GCFC]

They are the sheet row, so 10 is row 10 of the sheet.
XLookup like Index returns not just the value in the cell but the actual cell, so you can use it inside other functions.
eg =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10)) > Example 6 > XLOOKUP function - Microsoft Support

I get 179 different Row references.

 

[Alex Blakenburg]

I get 179 different Row references.

The aim was to identify if a formula was returning a different row than you were expecting and hence not the row that contains the expected blank in the return column.
eg
if you were expecting K9 to return "" and it was in fact returning a number / date then firstly is the number or date you are getting 0 or 0/01/1900 ?
if not then try my Row suggestion using K9 and using the Row no check if that was actually the line you were expecting it to get and does it have a blank in column A.

You could try and filter Sheet2 on the date in K9 and see if you get more than 1 value in the filtered data.