Ozgrid Excel Newsletter. Excel Newsletter Archives

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.

Excel Tips | EXCEL VBA TIPS

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

Excel VBA Macro Codes

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 | Multi-Table 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.

SYNTAX

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.
$A$1:$D$9 is the Table_Range.
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 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 special@ozgrid.com 31 days after purchase date.

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