[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

Hello,

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.

Thanks


(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])


Definitions:

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.

0
Steve_Brady
Asked:
Steve_Brady
  • 3
  • 2
3 Solutions
 
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.
0
 
redmondbCommented:
Steve,

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.

Regards,
Brian.
0
 
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:
=A2:INDEX(A:A,counta(A:A))
is semi-volatile.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now