Excel functions =MATCH() vs. =INDEX()

Posted on 2011-10-25
Last Modified: 2012-05-12

I frequently find myself confused by the Excel functions =MATCH() & =INDEX().  It's not uncommon that I realize I need one or the other but I am not sure which one is appropriate (or at least, best).  Today, in another thread, the question I asked resulted in one expert suggesting a solution using =INDEX() and another expert suggesting a solution using =MATCH().  That, of course, brought this issue to mind once again.

I would appreciate some comments regarding how to think about these two functions in relation to each other and maybe some thoughts about when to use one vs. the other, etc.


(Note:  I'm including these definitions and syntax, etc., for my own future reference.)

=MATCH()...searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
Syntax:  MATCH(lookup_value, lookup_array, [match_type])

=INDEX()...returns a value or the reference to a value from within a table or range.  There are two forms of the INDEX function: the array form and the reference form.

=INDEX() [Array form]...returns the value of an element in a table or an array, selected by the row and column number indexes.
Syntax:  INDEX(array, row_num, [column_num])

=INDEX() [Reference form]...returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
Syntax:  INDEX(reference, row_num, [column_num], [area_num])


Array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.

Range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.

Question by:Steve_Brady
    LVL 85

    Accepted Solution

    The two are really quite different. INDEX returns a value (or reference to a cell) from a table or array; MATCH only returns the position of a value in a row or column.
    LVL 26

    Assisted Solution


    Not for points...

    From force of habit, I tend to use Offset+Match (as you've seen). However, Index+Match has the advantage that Index is not volatile, so those cells are only recalculated if they (or their precedents) have changed. In contrast, Offset recalculates whenever a recalculation is done.

    LVL 85

    Assisted Solution

    by:Rory Archibald
    Actually, INDEX is semi-volatile (ie recalculates when the workbook is opened) for the particular syntax where it returns a reference as the extent of a range. Eg:
    is semi-volatile.
    LVL 26

    Expert Comment

    BTW, you might get more relevant responses if you accepted Rory's answer and started a new one titled "Excel functions =OFFSET() vs. =INDEX()".

    Author Closing Comment

    Thanks -- great responses!
    LVL 26

    Expert Comment

    Thanks, Steve! (And Rory, your semi-volatility point was new to me.)

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Running Out of IP Addresses 9 99
    make top menus bigger font 3 25
    Excel formula 5 32
    cannot scroll down in excel 5 0
    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now