+ Reply to Thread
Results 1 to 6 of 6

INDEX & MATCH vs OFFSET & MATCH

  1. #1
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    773

    INDEX & MATCH vs OFFSET & MATCH

    Hi,

    I understand that you can create very flexible lookups by linking either the INDEX or OFFSET functions with the MATCH function.

    Are these options equally effective or is one pair better than the other?

    Thanks
    Last edited by andrewc; 11-22-2013 at 12:52 PM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: INDEX & MATCH vs OFFSET & MATCH

    I think that INDEX is usually considered better because, unlike OFFSET, INDEX is not volatile, so should be quicker and more efficient in general. OFFSET also doesn't work with closed workbooks
    Audere est facere

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: INDEX & MATCH vs OFFSET & MATCH

    When you use OFFSET your workbook will always recalculate when there are any changes in your workbook and when you closing workbook.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: INDEX & MATCH vs OFFSET & MATCH

    There are many options available in Excel. These options may include different function and combinations. INDEX/OFFSET/MATCH/INDIRECT/VLOOKUP/CHOOSE/LOOKUP/HLOOKUP and the list is not finished. Ultimately it all depends on what is the most suitable for your project.
    Last edited by AlKey; 11-22-2013 at 01:28 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    773

    Re: INDEX & MATCH vs OFFSET & MATCH

    Thank you!

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: INDEX & MATCH vs OFFSET & MATCH

    Appreciate your feedback!

+ 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. Index match offset to get subsequent index values in a column
    By Andrew_Step in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2013, 02:55 PM
  2. Offset/Match vs. Index/Match
    By amartino44 in forum Excel General
    Replies: 5
    Last Post: 12-27-2012, 01:31 PM
  3. [SOLVED] index match with row information offset from the match cell
    By smls in forum Excel General
    Replies: 7
    Last Post: 08-30-2012, 09:48 AM
  4. Replies: 2
    Last Post: 03-16-2012, 12:03 PM
  5. [SOLVED] Index, Match, Offset? Not sure which to use
    By KL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 06:05 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