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

Ozgrid, Experts in Microsoft Excel Spreadsheets

INDEX & MATCH Functions/Formulas

| | Information Helpful? Why Not Donate.

 

 Flexible Excel Lookup Combination

Got any Excel Questions? Excel Help & Excel Video Tutorials

SEE ALSO: Vlookup Vlookup Across Worksheets | 5 Condition Vlookup | 4 Condition Vlookup | 3 Condition Vlookup | 2 Condition Vlookup | How to stop the #N/A! error | Lookup Any Occurrence in Any Table Column || Hlookup Formula || Left Lookup in Excel || Excel Lookup Functions | Multi-Table Lookup

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates