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


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.

Who is Participating?
Rory ArchibaldCommented:
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.

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.

Rory ArchibaldCommented:
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.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

BTW, you might get more relevant responses if you accepted Rory's answer and started a new one titled "Excel functions =OFFSET() vs. =INDEX()".
Steve_BradyAuthor Commented:
Thanks -- great responses!
Thanks, Steve! (And Rory, your semi-volatility point was new to me.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.