Xlookup blank cells - doesn't return error!

Originally Posted by Jedab

. . . 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.