+ Reply to Thread
Results 1 to 10 of 10

Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,900

    Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    As an Office Insider, I now have two new functions: XLOOKUP and XMATCH.

    These look really useful. Take a look here: https://techcommunity.microsoft.com/...UP/ba-p/811376
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    I read an article on it, and it looks really cool and useful!

    (I especially like the wildcard match feature, where you can search based on a partial string!)

    Unfortunately my office is not upgrading to Office 365 for the foreseeable future, so I'll probably never get to use it. Are there any homemade UDFs out there that replicate XLookup?

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    1,304

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    Same as above, we wont be getting it anytime soon, although it does look useful, The searching in reverse and the having a column returned from the left of the column the match is made on seemed useful. We did have hopes it might replace an index, match, match formula before we read the article, but alas this is not the case!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,127

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    FWIW, you can already use wildcards with VLOOKUP or MATCH, as long as you're using exact match (perhaps a little confusingly).
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  5. #5
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    Yes you are right, I didn't know that. I just tried it and it actually worked.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,178

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    A couple of observations -- noting that I am not an Office Insider, so I cannot actually use these functions (and probably won't until LibreOffice and others implement them).

    1) XLOOKUP() -- unlike all previous lookup functions -- defaults to exact match (the most common option) rather than approximate match.
    2) As before, the examples are all for the "exact match" option, which makes it more difficult for users to learn how to use the approximate match options.
    3) If you choose to use the approximate match options, your lookup table must still be sorted in ascending or descending order (a separate step outside of the lookup function).
    4) I am wondering how XLOOKUP()'s performance compares to VLOOKUP()/HLOOKUP(). It is somewhat common thing here -- especially when a user comes here complaining of a slow spreadsheet -- to find that lookups are a big part of the spreadsheet -- especially when those lookups are "exact match" linear lookups. I wonder if XLOOKUP() will be able to perform lookups faster, or if its performance is going to be similar to VLOOKUP/HLOOKUP. If it is not substantially faster, I could see situations where INDEX() and MATCH() (in separate cells) could be preferable where you perform the slow lookup with MATCH() in one cell, then return multiple values based on the one lookup. Maybe XLOOKUP() will not completely replace these other functions?

    Just some thoughts. As noted, I probably won't ever get to use it in Excel, so maybe it doesn't matter.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,127

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    They'd already made performance improvements to the lookup functions (see here) so I'd guess those are incorporated into the new functions.

  8. #8
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    XLookup also has a feature that it can search from bottom to top, however the few times I needed to do that I would just sort the list the way I wanted before doing the VLookup.

  9. #9
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,716

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    I always think that if I need to use a lookup / match function, then I should really be using Power Query / Power Pivot instead...
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,900

    Re: Forget VLOOKUP, HLOOKUP & INDEX MATCH - XLOOKUP & XMATCH are Coming!

    Great response, guys - really positive!!! LOL!

    I shall be giving them a whirl when they arrive in Office 365 at work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1