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

GCFC

New Member
Joined
Feb 16, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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 ?
 
Upvote 0
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?
 
Upvote 0
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.
Excel Formula:
=ROW(XLOOKUP(K9,Sheet2!$K$4:$K$1128,Sheet2!$A$4:$A$1128))
 
Upvote 0
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.
Excel Formula:
=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)?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top