modified VLOOKUP in VBA excel

Question

I am trying to modify VLOOKUP function in VBA, but because I work in VBA for the first time a dont know how to do certain things. I want to apply vlookup for e.g. 200 cells in a column at one moment. I found it can be done using for cycle but it didnt work for me. Lets say we have three columns. In first, there are lookupvalues, in second there are some values and in third there shall be lookuped values. Lets say, I want to lookup values only in that rows in which value in second column is zero. And important thing to repeat, I want it by entering formula only in one cell. Can anybody help me? link for image

you want a vlookup that works like countifs? am i correct? — L42
I cant see anything important what has this function that I want in common with countif. But its possible I just dont understand you. — lulish89
countifs in excel 2007 and up have multiple criteria. as i understand, you want vlookup version with multiple criteria right? — L42
I added image for better understanding of what I need, I believe it is understandable enought from it. — lulish89
How about: =IF(B1=0,VLOOKUP(A1,I1:J20,2,FALSE),""). This only performs the look up where there is a 0 in column B — Alex P

L42 L42 · Accepted Answer · 2013-12-19T09:13:03

Then try this:

Function FLOOKUP(lookup_value, table_array As Range, col_index_num As Long, _
                  range_lookup As Boolean, Optional ref_value, Optional criteria) As Variant

Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr, find_value As String
Dim my_range As Range
Dim row_count, col_count As Long
Dim check As Boolean

col_count = table_array.Columns.Count
find_value = lookup_value

If col_index_num >= 0 Then
    Set my_range = table_array.Resize(, 1)
Else
    Set my_range = table_array.Resize(, 1).Offset(0, col_count - 1)
End If

With my_range
    row_count = .Cells.Count
    If row_count = 1048576 Then row_count = .Cells(.Cells.Count).End(xlUp).Row
End With

Set my_range = my_range.Resize(row_count)
Set LastCell = my_range.Cells(my_range.Cells.Count)

If range_lookup Then
    Set FoundCell = my_range.Find(what:=find_value, after:=LastCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
Else
    Set FoundCell = my_range.Find(what:=find_value, after:=LastCell, LookIn:=xlFormulas, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
End If

If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
    If IsNumeric(col_index_num) And Abs(col_index_num) <= col_count Then
        Select Case col_index_num
        Case Is > 0
            If IsMissing(ref_value) Then
                FLOOKUP = FoundCell.Offset(0, col_index_num - 1).Value
            Else
                If ref_value = criteria Then
                    FLOOKUP = FoundCell.Offset(0, col_index_num - 1).Value
                Else
                    FLOOKUP = CVErr(xlErrNA)
                    Exit Function
                End If
            End If
        Case Is < 0
            If IsMissing(ref_value) Then
                FLOOKUP = FoundCell.Offset(0, col_index_num + 1).Value
            Else
                If ref_value = criteria Then
                    FLOOKUP = FoundCell.Offset(0, col_index_num + 1).Value
                Else
                    FLOOKUP = CVErr(xlErrNA)
                    Exit Function
                End If
            End If
        End Select
        Exit Function
    Else
        FLOOKUP = CVErr(xlErrRef)
        Exit Function
    End If
Else
    FLOOKUP = CVErr(xlErrNA)
    Exit Function
End If

End Function

Still needs refining but i how this gets you started.

SYNTAX:

FLOOKUP (lookup_value, table_array, col_index_num, range_lookup, [ref_value], [criteria])

The first four argument is same as Vlookup but with range_lookup not optional.
The remaining two(2) is optional.
ref_value is the value you wish to compare to (in your case values found in Column B).
criteria is the test criteria. (in your case 0)

Here's the screen shot: sample image

modified VLOOKUP in VBA excel

1 Answers