+ Reply to Thread
Results 1 to 4 of 4

Index/Match vs Xlookup

  1. #1
    Forum Contributor
    Join Date
    04-09-2013
    Location
    City, World
    MS-Off Ver
    Office 365
    Posts
    213

    Index/Match vs Xlookup

    Hi all
    I do not see why I need to use Index or Match function anymore since Xlookup can do the job even simpler. Are they any examples in which I can not use Xlookup and I have to use Index or Match?
    Thank you very much.

  2. #2
    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,190

    Re: Index/Match vs Xlookup

    This is one of those questions that is hellishly difficult to answer!

    In brief, yes, there will be occasions when INDEX MATCH might still be preferable, however it will be in complex situations.

    The slightly longer answer is that you should consider each function a part of your toolkit. XLOOKUP, VLOOKUP and HLOOKUP, LOOKUP and INDEX MATCH all still have their part to play.

    The bottom line is this: if you can do something easier with XLOOKUP than you once did with INDEX MATCH, then go for it. I would not waste time 'updating' formulae just so that they use XLOOKUP instead, though: just start using it in preference until you hit a stumbling block.
    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.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Index/Match vs Xlookup

    Another consideration is that you might need backwards-compatibility, i.e. if your file is to be used by someone who does not have XLOOKUP available. YOU would not have it if you are still using XL2010, as your profile states, so please update your profile by clicking on User CP in the top menu.

    Pete

  4. #4
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    366

    Re: Index/Match vs Xlookup

    Quote Originally Posted by Pete_UK View Post
    Another consideration is that you might need backwards-compatibility [...] would not have it if you are still using XL2010
    .... Or any standalone version before Excel 2021(!), according to the support webpage.

    And efficiency might be another issue. (Although I wouldn't lose sleep over it, usually.)

    Some people claim that VLOOKUP is inefficient. I think they claim that it reads the entire multicolumn range (second parameter) into memory, unnecessarily.

    I never vetted such claims myself. I usually take them with a grain of salt.

    But it begs the question: Does XLOOKUP read the entire "return range" (third parameter) into memory, again unnecessarily?

+ 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. XLOOKUP vs INDEX & MATCH?
    By andrewc in forum Excel General
    Replies: 9
    Last Post: 08-26-2021, 10:59 AM
  2. Second to last exact match using xlookup
    By Neeendre in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-24-2020, 10:07 AM
  3. [SOLVED] Using IF, Xlookup and Match/search, to find value in array.
    By Lee_of_Excel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2020, 12:09 AM
  4. Replies: 14
    Last Post: 09-28-2019, 10:32 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 AM

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