<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

INDEX & MATCH Functions/Formulas

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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_arrayof $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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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