+ Reply to Thread
Results 1 to 15 of 15

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
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,191

    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!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  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
    365 and rarely 2016
    Posts
    3,009

    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
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,315

    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

  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
    14,479

    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
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,315

    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
    6,284

    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
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,191

    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.

  11. #11
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

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

    They are available in 365.
    if i have some porridge i can share a spoon or two. Soft is another matter. i can't work with those functions in 2016 with a file, created in 365. I even can't bye a small adding for say $1 or less. it is not for customers. Thomas Jefferson was right:
    If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. Its peculiar character, too, is that no one possesses the less, because every other possesses the whole of it. He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me. That ideas should freely spread from one to another over the globe, for the moral and mutual instruction of man, and improvement of his condition, seems to have been peculiarly and benevolently designed by nature, when she made them Ö incapable of confinement or exclusive appropriation.

  12. #12
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

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

    Quote Originally Posted by tim201110 View Post
    They are available in 365.
    Are you sure that you aren't in the "Insider" program as xlookup isn't available in my regular version yet?
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown

  13. #13
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

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


  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,191

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

    Scroll down, Tim, and read the note. It is still not generally available.

  15. #15
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

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

    Obviously Tim didn't think I knew what functions my version of Excel has

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using VLOOKUP and HLOOKUP or use INDEX and MATCH
    By jlambert36 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-30-2018, 01:28 PM
  2. [SOLVED] Vlookup or HLookup or index match, IDK anymore
    By Mechanical Pencil in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2016, 08:11 PM
  3. [SOLVED] Sumif / index / match / vlookup / hlookup
    By jkle4596 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-18-2016, 05:12 PM
  4. Index, Match, Vlookup, Hlookup or what?
    By ovgarcia24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2014, 07:07 PM
  5. Vlookup with Hlookup or Index with Match?
    By brad_x81 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-08-2013, 06:40 AM
  6. Vlookup, HLookup, Index, Match, oh my! Help me.
    By ryanl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2013, 09:29 PM
  7. Vlookup/hlookup/match/index?
    By margggggg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 07:46 PM

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