Looks like the XLOOKUP function will only be available to Office 365 subscribers. Say it ain't so.
Pete
Looks like the XLOOKUP function will only be available to Office 365 subscribers. Say it ain't so.
Pete
Fraid it is so, although it maybe available in the next stand alone version
yups.. not in standalone EXcel 2019 or lower so have to wait for Excel 2022 (if a next standalone is ever released)
it is not even available for all 365 users I think, only for "Insiders"
at least that is what is said in the helptext
https://support.office.com/en-us/art...9-88eae8bf5929
Given there's going to be a FILTER function a la Google Sheets, what's the appeal of XLOOKUP?
FWIW, Excel Online already has FILTER, and it can be used as =INDEX(FILTER(BigRange,ISNUMBER(SEARCH(some_pattern,INDEX(BigRange,0,k)))),n) to return the entire nth row of BigRange in which some_pattern matches the column k value in BigRange. To the extent XLOOKUP also replaces HLOOKUP, it's handier than FILTER, but for VLOOKUP functionality, INDEX(FILTER(...),...) does things even XLOOKUP can't.
hringrv:
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.
It'd work for me.
Pete
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.
Last edited by hrlngrv; 01-13-2020 at 09:30 PM. Reason: elaboration
XLOOKUP(x,y,z,"",0) == LET(f,FILTER(z,y=x),IF(ISERROR(f),"",f))
XLOOKUP(x,y,z,"",0,-1) == LET(f,FILTER(z,y=x),IF(ISERROR(f),"",INDEX(f,ROWS(f),0)))
Yes, FILTER is longer in these situations. Now say you want the 2nd from the last item in z.
LET(f,FILTER(z,y=x),IF(ISERROR(f),"",IF(ROWS(f)>1,INDEX(f,ROWS(f)-1,0))))
Kindly show the more compact equivalent using XLOOKUP.
XLOOKUP is better for some things, but for it's likely most frequent uses, it'd be inferior to FILTER.
Why are you using iserror when filter has it's own inbuilt error handling?
Because I hadn't paid enough attention to its documentation or argument hints. Thanks for pointing that out. Makes it simpler.
FILTER(z,y=x,"")
LET(f,FILTER(z,y=x,{""}),INDEX(f,ROWS(f),0))
LET(f,FILTER(z,y=x,{""}),IF(ROWS(f)>1,INDEX(f,ROWS(f)-1,0),""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks