# Office 365 >  >  Xlookup blank cells - doesn't return error!

## Jedab

I was excited to start using *XLOOKUP*, but am distraught to see that it *"looks up" blank cells and 'matches' them to the first blank cell found in the lookup array!*
If you're like me an your lookup arrays are usually full-columns, which always have blank cells at the bottom, this is NOT GOOD!

When one uses VLOOKUP, and the lookup cell is blank, it would return an error, which was great, since one could nest the vlookup formula within the 'iferror' formula like this, 

=iferror(vlookup(...),"")

so if the lookup was blank the result would be blank.  SO this iferror formula would address any non-found values or blanks, and if you forgot the iferror formula the 'results' would remind you with their #N/A values.

Xlookup addresses non-found values within the formula itself ('if not found' parameter), which is nice, but since it matches blank lookup cells to blank cells in the lookup array, it will still need to be sandwhiched with another formula; I'm thinking:

=if(_'lookupcell'_="","",xlookup(...)) 

which certianly detracts from the excitement.  Also, if one forgot to nest a vlookup formula within an iferror formula, the error results would serve as a reminder. For Xlookup, howver, if one forgets to address blanks, it will likely generate clean-looking results that are not desired.  For this reason I would prefer Microsoft change the xlookup to return error (or the not found parameter) if the lookup cell itself is blank.  Anyone else agree?  Any other suggestions or workarounds towards addressing my blank-blank match concern?

xlookup of blank cell.JPG

----------


## AliGW

Welcome to the forum.  :Smilie: 

This query should not have had a poll added to it - your poll is closed.

If you wish to raise this with Microsoft, you need to use their User Voice facility - our forum is not the right platform for this.

----------


## AliGW

Attach the workbook, please - instructions at the top of the page.

----------


## hrlngrv

> . . . If you're like me an your lookup arrays are usually full-columns, . . .



Spilled formulas and dynamic arrays will REQUIRE Excel users to abandon some sloppy practices of the past. Unnecessary entire column/row references may be the first such sloppy practice requiring abandonment. In a way it's like real programming in which dynamic memory allocation calls are never checked for successful completion. Usually OK, gastly in the rare instances in which foreseeable errors weren't avoided.





> . . . For this reason I would prefer Microsoft change the xlookup to . . .



FWLIW, Lotus 1-2-3 Release 5 (which I keep around on a VM for minor amusement and discussions like this) returns 1 for @MOD(2^44,3) and 2 for @MOD(2^45,3) while Excel returns #NUM! for =MOD(2^44,3) and =MOD(2^45,3). The modulus of 2^52 to 3 is within IEEE 754 double-precision floating point specs, so EVERY PC or Mac sold in the last 15 years has hardware floating point able to calculate those Excel MOD calls correctly. Evidence Excel's developers DON'T USE hardware floating point calls for modulus. This has been a bug in Excel going back to the mid 1980s. Don't hold your breath waiting for MSFT to change XLOOKUP in any appreciable way.

Anyway, to the point at hand, I'd argue XLOOKUP is working correctly: seeking the 1st/topmost cell in A:A equal to E2. FWIW, =XMATCH(E2,A:A) returns 5. This is indeed a departure from MATCH semantics, since =MATCH(E2,A:A) and =MATCH(E2,A:A,0) both return #N/A. For me, this was always problematic, meaning if one really did want to match blank against blanks, one had to use MATCH(TRUE,INDEX(ISBLANK(range),0),0).

Like it or not, you're going to have to start referring only to the cells you need. You could do so dynamically with

=XLOOKUP(E2,A$2:INDEX(A:A,LOOKUP(1,0/NOT(ISBLANK(A:A)),ROW(A:A))),B$2:INDEX(B:B,LOOKUP(1,0/NOT(ISBLANK(B:B)),ROW(B:B))),"!")

IMO, it'd be better to define names like UsedColA referring to *=!$A$1:INDEX(!A:A,LOOKUP(1,0/NOT(ISBLANK(!$A:$A)),ROW(!$A:$A)))*, and similarly for other columns, then

=XLOOKUP(E2,UsedColA,UsedColB,"no match")

Finally, there's always the option of sticking with INDEX+MATCH and letting XLOOKUP and XMATCH die slow painful deaths from lack of use due to unexpected/undesired semantics.

----------

