Lookup Any Occurrence in Any Table Column

Return The Corresponding Cell to the Left or Right

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

See Also: Vlookup | Index Match | Custom Functions

Excel is rich in lookup functions but lacks if you need to lookup the nth occurrence and return the corresponding cell to the left or right of the match.

Try this Custom Function, which should work in Excel 2000 or above.

Function Lookup_Occurence(To_find, Table_array As Range, _
    Look_in_col As Long, Offset_col, Occurrence As Long, _
    Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
    
Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt

If Part_cell_match = False Then
    xlLook = xlWhole
Else
   xlLook = xlPart
End If


Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)
    On Error Resume Next
        For lLoop = 1 To Occurrence
          Set rFound = Table_array.Columns(Look_in_col).Find _
            (What:=To_find, After:=rFound, LookAt:=xlLook, _
                MatchCase:=Case_sensitive)
        Next lLoop
     On Error GoTo 0
     
     Lookup_Occurrence = rFound.Offset(0, Offset_col)
        
End Function

Used in any cell like;
=Lookup_Occurrence("Jan",$A$1:$C$5000,2,-1,3)

Where;
Jan is the value to find. (To_find)

$A$1:$C$5000 is data table. (Table_array)

2 is the column (relative to Table_array) of the data table to look in, column B in this case. (Look_in_column)

-1 is the offset from the found occurrence (Column A in this case). It can be positive or negative. (Offset_column)

3 is the occurrence of Jan to find in Column B. (Occurrence)

Or, if you use TRUE, TRUE (the default if omitted is FALSE, FALSE) for the last 2 optional arguments (Case_sensitive and Part_cell_match) it becomes case sensitive and looks for part cell matches. That is, a cell housing Jan. Is the next month. or The next month is Jan. or Next month is Jan, followed by Feb. will all be considered matches.

If the number of occurrences (based on your arguments) is not possible, the Function returns the last matching occurrence as the match.

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.

Instant Download and Money Back Guarantee on Most Software

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