Feedback by UserVoice

Greg

My feedback

  1. 746 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Greg commented  · 

    ' nXLookup - VLOOKUP and HLOOKUP replacement function
    ' Parameters:
    ' - LookFor, What you are searching for
    ' - LookIn, the Range to search within
    ' - RowOffset, Simmilar to col_index_num in VLOOKUP except 0 is the first column and can use negative values
    ' - ColOffset, Simmilar to row_index_num in HLOOKUP except 0 is the first row and can ues neg values
    ' - Occurrence, Get the Nth Match
    ' - LoopValues, Causes the matching to wrap arround so that when set to true if there are 5 matches and you
    ' ask for the 7th then you'll get the 2nd
    ' - FindLookIn, 1 = xlValues - Search within the evaluated cell values
    ' 2 = xlFormulas - Search within the non-evaluated cell values
    ' 3 = xlComments - Search within the cell comments
    ' - LookAtWhole, true = match only cells where the whole contents match, false = partial match
    ' - SearchByColumns, true = search column by column, false = search row by row
    ' - SearchNext, true = top down when by columns or left to right when by rows
    ' false = bottom up when by columns or right to left when by rows
    ' - MatchCase, true = case-sensitive matching, false = non-case-sensitive
    ' - IsVolatile, true = updates when data updates, false = updates when formula cell is recalculated
    ' Note: I didn't implement the SearchFormat because it could be a bit tricky via a worksheet function, and
    ' would probably be easier to do on a case by case basis as needed
    ' Also: When FindLookIn = 2 or 3, you'll most likely want to pass False to LookAtWhole instead of defaulting to True.
    Public Function nXLookup(LookFor As Variant, LookIn As Range, Optional RowOffset As Long = 0, Optional ColumnOffset As Long = 0, Optional Occurrence As Long = 1, Optional LoopValues As Boolean = False, _
    Optional FindLookIn As Long = 1, Optional LookAtWhole As Boolean = True, Optional SearchByColumns As Boolean = True, _
    Optional SearchNext As Boolean = True, Optional MatchCase As Boolean = False, Optional IsVolatile As Boolean = True) As Variant
    Application.Volatile IsVolatile
    nXLookup = CVErr(xlErrValue)
    If FindLookIn < 1 Or 3 < FindLookIn Or Occurrence < 1 Then Exit Function

    Dim f As Range: Set f = LookIn.Find( _
    What:=LookFor, _
    After:=LookIn.Cells(IIf(SearchNext, LookIn.Cells.count, 1)), _
    LookIn:=Choose(FindLookIn, xlValues, xlFormulas, xlComments), _
    LookAt:=IIf(LookAtWhole, xlWhole, xlPart), _
    SearchOrder:=IIf(SearchByColumns, xlByColumns, xlByRows), _
    SearchDirection:=IIf(SearchNext, xlNext, xlPrevious), _
    MatchCase:=MatchCase _
    )
    nXLookup = CVErr(xlErrNA)
    If f Is Nothing Then Exit Function
    Dim FirstAddr As String: FirstAddr = f.Address
    Dim count As Long: count = 1
    Do Until (count = Occurrence) Or ((Not LoopValues) And f.Address = FirstAddr And count <> 1)
    Set f = LookIn.Find( _
    What:=LookFor, _
    After:=f, _
    LookIn:=Choose(FindLookIn, xlValues, xlFormulas, xlComments), _
    LookAt:=IIf(LookAtWhole, xlWhole, xlPart), _
    SearchOrder:=IIf(SearchByColumns, xlByColumns, xlByRows), _
    SearchDirection:=IIf(SearchNext, xlNext, xlPrevious), _
    MatchCase:=MatchCase _
    )
    count = count + 1
    Loop
    If count <> Occurrence Then Exit Function

    Dim MaxRows As Long: MaxRows = LookIn.Parent.Rows.count
    Dim MaxCols As Long: MaxCols = LookIn.Parent.Columns.count
    Dim OffROW As Long: OffROW = f.Row + RowOffset
    Dim OffCOL As Long: OffCOL = f.Column + ColumnOffset
    If Not (0 < OffROW And OffROW <= MaxRows And 0 < OffCOL And OffCOL < MaxCols) Then
    nXLookup = CVErr(xlErrRef)
    Else
    Set nXLookup = f.Offset(RowOffset, ColumnOffset)
    End If
    End Function

    Greg supported this idea  · 

Feedback and Knowledge Base