MATCH and INDEX Functions
INDEX and MATCH belong to the Lookup and Reference section of Excel functions. INDEX returns a value or reference of the cell at the intersection of a particular row and column in a given range. MATCH returns the relative position of an item in an array that matches a specified value in a specified order.
MATCH
Syntax: =MATCH(Criteria,WhereToLook,TypeOfMatch)
By itself, MATCH returns a number that represents the place where the criteria can be found.
In this example, the MATCH function has as a criterion, ABC12250. The range is the first column and the 0 indicates it is looking for an exact match. The answer it returns is 4, which indicates that the fourth item in the list equals the criteria.
There are three possible values for the third argument: 0 for an exact match, and 1 for an exact or lower list value. These two operate the same way as in the VLOOKUP function. The third value is -1 and allows the MATCH function to perform an approximate match on a reverse ordered list.
1 ~ Exact match or next lower value in a low to high sorted list.
0 ~ Exact match and list does not need to be sorted.
-1 ~ Exact match or next higher value in a high to low sorted list.
MATCH will also search a horizontal list.
INDEX
Syntax: =INDEX(TableArray,Y_Coordinate,X_Coordinate)
The TableArray is a two dimensional table not including any column or row headers. The Y_Coordinate represents the row, or vertical axis. The X_Coordinate represents the column or horizontal axis. With a Y_Coordinate of 4 and an X_Coordinate of 2, INDEX would return the fourth item down the second column.
The TableArray is represented in the purple box and does not include any headers associated with the table. The Y and the X coordinates can also be represented by cell values, or by the MATCH function.
When MATCH is combined with INDEX, you would get a formula that looks like:
=INDEX(B3:N21,MATCH(B24,A3:A21,0),MATCH(B25,B2:N2,0))
In this example, the two MATCH criteria have been placed in B24 and B25. If you want to find a different amount, all you would need to do is change the contents of either or both of those cells.
You could also use Data Validation to make a dynamic lookup.
For B24 and B25, we are going to substitute drop down lookup lists based on column A and row 2. Place your cursor where you want to insert the first lookup list (example: B24)
Navigation: Data (Ribbon) > Data Validation > Data Validation. The Data Validation dialog displays.
Select List from the Allow dropdown menu.
Place your cursor in the Source box then highlight the list starting at A3:A20. Excel will lock the list range automatically. Click OK.
There is now a dropdown list displaying the values in the A column.
Repeat the process for the X coordinate. The only difference is that the Source is a horizontal range. Excel won’t have a problem with this.
While the formula hasn’t changed, the lookup criteria is now dynamic, and you can change the result with a simple click of the dropdown list.