ALL YOUR EXCEL NEEDS 
FREE Excel STUFF 
Excel Newsletter 
Advanced Search 
PRODUCTS 
Development 
Contact Us 

NEW! Free Questions, Newsletter ONLY. You need a username and password from the Excel Help forum. If you do not have one, join here for free.
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  MultiTable 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.
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
More Custom Functions. 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  MultiTable Lookup
This Custom Function (UDF) will look in any column, unless specified, for the nth occurrence of the specified value and return the corresponding value x rows above or below the found value to the left or right.
OzgridLookup(Find_Val,Occurence,Table_Range,Offset_Cols, Column_Lookin,Row_Offset)
The last 2 arguments are optional. Any Table_Range should include the 1st row which should be headings.
Function OzgridLookup(Find_Val As Variant, Occurrence As Long, Table_Range As Range, _ Offset_Cols As Long, Optional Column_Lookin As Long, Optional Row_Offset As Long) As Variant Dim lLoop As Long Dim FoundCell As Range If Column_Lookin = 0 Then 'No column # specified With Table_Range 'Top left cell has Find_Val & Occurence is 1 If Table_Range.Cells(1, 1) = Find_Val And Occurence = 1 Then OzgridLookup = .Cells(1, 1)(1, Offset_Cols + 1) Exit Function 'All done :) Else 'No column # specified so search all for _ nth Occurrence reading left to right Set FoundCell = .Cells(1, 1) 'Set cell variable for Find start For lLoop = 1 To Occurrence 'Loop as many times as Occurrences _ and each time Set "FoundCell" to start next Find from Set FoundCell = _ Table_Range.Find(What:=Find_Val, After:=FoundCell, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlRows, SearchDirection:=xlNext) Next lLoop End If End With Else 'column # specified With Table_Range.Columns(Column_Lookin) 'Work with column # specified Set FoundCell = .Cells(1, 1) 'Set cell variable for Find start For lLoop = 1 To Occurrence 'Loop as many times as Occurrences _ and each time Set "FoundCell" to start next Find from Set FoundCell = _ Table_Range.Find(What:=Find_Val, After:=FoundCell, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlRows, SearchDirection:=xlNext) Next lLoop End With End If OzgridLookup = FoundCell.Offset(Row_Offset, Offset_Cols) End Function
Used in any cell like;
=OzgridLookup("Project 1",2,$A$1:$D$9,3,1,1)
OzgridLookup(Find_Val,Occurence,Table_Range,Offset_Cols, Column_Lookin,Row_Offset)
Where "Project 1" is the Find_Val.
2 is the Occurrence.
3 is the Offset_Cols
1 is the optional Column_Lookin
1 is the optional Row_Offset
Special! Free Choice of Complete Excel Training Course OR Excel Addins 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.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft