# INDEX & MATCH Functions/Formulas

## Flexible Excel Lookup Combination

Got any Excel Questions? Excel Help & Excel Video Tutorials

While the Vlookup Function is very useful, it cannot look in any Column, only the 1st. Also, it cannot offset x columns to the left or return the value x rows before or after the found value. An INDEX & MATCH combo will allow for all of this flexibility.

### SYNTAX

INDEX(array,row_num,column_num)

MATCH(lookup_value, lookup_array, [match_type])

Consider the above table and that we need to find out the "Project" corresponding to the "Original Project Start Date" of the 4/08/2009. We would use;

=INDEX(\$A\$1:\$D\$9,MATCH(DATE(2009,8,4),\$B\$1:\$B\$9,0),1) would return "Project 4". Where the dates are in the European format of d/mm/yyyy. The 1 at the end tells INDEX to return the cell from the 1st Column of the array \$A\$1:\$D\$9. The Match is told to return the relative row number of the DATE(2009,8,4) in the  lookup_array of \$B\$1:\$B\$9. The use of zero (0) in the MATCH functions tells it to find and exact matching date.

Now consider we need to find the the "Project" x rows before or after the "Original Project Start Date" of the 4/08/2009. We would use;

=INDEX(\$A\$1:\$D\$9,MATCH(DATE(2009,8,4),\$B\$1:\$B\$9,0)-1,1) to return the "Project" 1 row before the "Original Project Start Date" of the 4/08/2009

=INDEX(\$A\$1:\$D\$9,MATCH(DATE(2009,8,4),\$B\$1:\$B\$9,0)+1,1) to return the "Project" 1 row after the "Original Project Start Date" of the 4/08/2009

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.