Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

INDEX & MATCH Functions/Formulas

| | Information Helpful? Why Not Donate.

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.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft