XLOOKUP availability?

Originally Posted by PeteABC123

The attraction is that XLOOKUP can return data to the left of your lookup value column without a mile long effort, can seek from the bottom or top of a range, can look for the smallest value that it exceeds, or the largest.

Replaces HLOOKUP and how I currently use INDEX/MATCH. One stop shopping. . . .

Understood about returning data to left of lookup column, but so can FILTER, e.g., =INDEX(FILTER(A3:A1002,Z3:Z1002="something"),n) to return the nth match. FILTER also handles multiple criteria. OK, XLOOKUP's 2nd argument is handled as an array, so it could be (a=b)*(c=d) [AND] or SIGN((a=b)+(c=d)) [OR], but that implies calculating both (a=b) and (c=d). Dunno whether FILTER uses binary shortcuts. That is, for FILTER(x,a=b,c=d), if a given row in (a=b) is FALSE, does it still evaluate or bypass the corresponding row of (c=d)?

Yes, XLOOKUP has 4 different match modes, and maybe it's good that Exact and wildcards are separate modes. OTOH, -1 and 1 may be problematic if coupled with the wrong binary search mode. ADDED: seems to me that binary search modes should make match modes -1 and 1 unnecessary. Alternatively, match modes -1 and 1 could make specifying search mode unnecessary. Hard to see a compelling reason for match modes -1 or 1 when lookup_array isn't sorted, and I see that as a potentially EXTREMELY fruitful source of errors for text matching when there are stray trailing spaces. Indeed, 2 more match modes would have been handy: Exact and Wildcard also automatically RTrimming both lookup_value and lookup_array. Then again maybe also options to search for numeric lookup_value in text representations of numbers in lookup_array or vice versa.

XLOOKUP also replaces HLOOKUP, which FILTER doesn't, so XLOOKUP superior there.

All that said. I use HLOOKUP maybe 1/100 as frequently as VLOOKUP, and I usually have more need for finding 2nd and subsequent matches for which INDEX(FILTER(...),n) would be much more useful. That said, it's a pity INDEX doesn't take negative 2nd and subsequent arguments to index bottom to top, right to left.

Anyway, Excel Online already provides FILTER but not XLOOKUP, though I don't know whether or not that's because I'm an Office 365 subscriber.