INDEX and MATCH functions



INDEX and MATCH 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.

Syntax: =INDEX(table_array,y_coordinate,x_coordinate)

=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)

Together, these two functions allow you to create a multi-variable lookup.

Two MATCH functions will be substituted for the Y and X coordinates. Three pieces of information are required: an array or table; a Y axis reference; and an X axis reference. Order is important when writing the formula. It is very helpful (but not required) to name your three major ranges: source table, Y axis, and X axis).

=INDEX(table_array,MATCH(cell_reference,Y_Axis,0),MATCH(cell_reference,X_axis,0))

[pic]

In the picture above, the red box is the table_array; the blue box is the Y_axis; and the green box is the X_axis. You can see that the three boxes overlap in the upper left corner. The INDEX-MATCH formula always starts from the top left corner. You are instructing the formula to go down so many rows and across so many columns and select the value where the Y and the X intersect. So let’s take the formula apart to see how it works. Remember, you go DOWN and then ACROSS when using this formula.

Table_array is where all of the information is stored including the Y and X axes. It may be a cell reference (e.g., A1:F20) or a named range.

Cell_reference is the value you are looking find in the table_array. It can be a cell reference in the Y or X axis, a cell reference outside of Y or X that contains a value that can be matched to the Y or X axis, or it can be a “text” value that is contained in the Y or X axis.

Y_axis is the vertical search area. It may be a cell reference (e.g., A1:A20) or a named range.

X_axis is the horizontal search area. It may be a cell reference (e.g., A1:F1) or a named range.

0 indicates an exact match is required (1 = exact or next lowest value, -1 = exact or next highest value).

Using Data Validation to make your Formula interactive

What if you don’t want to have to rework your formula every time you want to search on a new Y or X axes? You can create a drop down menu for your Y and X axes and have your user select the values of interest. This is accomplished most easily by employing Data Validation using named ranges to define the lookup values.

As a matter of ease, I normally name my important ranges (e.g., table_array, Y_axis, X_axis) You can name the ranges any name you want. For example purposes I will be using the names above. Highlight the range you want to name. Click into the Name Box in the upper left corner of your spreadsheet. Type the name of your range. Press Enter.

[pic]

Do this for your table, Y axis, and X axis.

1. Place your cursor outside of your table_array where you want to place your first drop down menu. In the provided practice sheet, use C42 for the Y axis and E42 for the X axis.

2. Navigate to Data > Validation and from the Settings tab select List from the Allow drop down menu.

[pic]

3. In the Source entry line type the name of your first range, starting with the equal sign “=” (e.g., =Y_axis). Repeat for the second range. The result is that you have created two drop down boxes; one for each axis that has its own cell. That cell can be substituted in the formula for the cell_reference.

C42 (Y_axis) E42 (X_axis)

[pic] [pic]

4. Don’t forget to label what the Y_axis (Dept) and X_axis (Period) represent. Also, label the answer as Amount or Amount Expended (documenting your assumptions is considered a best practice).

Your formula will look something like:

=INDEX(table_array,MATCH(C42,Y_axis,0),MATCH(E42,X_axis,0)) where the C42 and E42 are the cell references for the data validation drop down.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download