Solved

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

Posted on 2011-10-25
214 Views
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

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.
0

LVL 26

Assisted Solution

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

LVL 85

Assisted Solution

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

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()".
0

Author Closing Comment

Thanks -- great responses!
0

LVL 26

Expert Comment

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

## Featured Post

### Suggested Solutions

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…