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.