Vba Vlookup(Faster Way)

[milan]

Respected All,
I want help With mentioned Vba Vlookup code Which i already got from this forum with little change that vllokup renturn value from Last match is this possible??
Also attached sample file & Link from where i found this code......
Please Help...

http://forum.chandoo.org/threads/faster-way-to-vlookup-in-vba.19029/

Sub LookmeUp()
Dim i As Integer
Dim j As Integer
Dim lr As Long
Dim var As Variant
Dim ar As Variant
Dim t As Double
t = Timer
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
ReDim var(1 To lr, 1 To 9) 'Change 9 to suit
ReDim ar(1 To 9, 1 To 1) 'Change 9 to suit

    For j = 1 To UBound(ar) 'Trap the position of the column
      ar(j, 1) = Sheet1.Rows(1).Find(Sheet3.Range("A" & j + 1), , , xlWhole).Column
    Next j
 
    For i = 2 To lr 'Trap the values of the Vlookup
      For j = 1 To UBound(ar)
            var(i - 1, j) = Application.VLookup(Cells(i, 1), Sheet1.[A:J], ar(j, 1), 0)
        Next j
    Next i
    Sheet2.Range("B2:J" & lr) = var
MsgBox Timer - t
End Sub

 

[NARAYANK991]

Hi ,

I am not able to understand what you wish to do.

Can you explain , in simple English , what you want done ?

Narayan

 

[Nitesh Khot]

Respected All,
I want help With mentioned Vba Vlookup code Which i already got from this forum with little change that vllokup renturn value from Last match is this possible??
Also attached sample file & Link from where i found this code......
Please Help...

http://forum.chandoo.org/threads/faster-way-to-vlookup-in-vba.19029/

Sub LookmeUp()
Dim i As Integer
Dim j As Integer
Dim lr As Long
Dim var As Variant
Dim ar As Variant
Dim t As Double
t = Timer
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
ReDim var(1 To lr, 1 To 9) 'Change 9 to suit
ReDim ar(1 To 9, 1 To 1) 'Change 9 to suit

    For j = 1 To UBound(ar) 'Trap the position of the column
      ar(j, 1) = Sheet1.Rows(1).Find(Sheet3.Range("A" & j + 1), , , xlWhole).Column
    Next j

    For i = 2 To lr 'Trap the values of the Vlookup
      For j = 1 To UBound(ar)
            var(i - 1, j) = Application.VLookup(Cells(i, 1), Sheet1.[A:J], ar(j, 1), 0)
        Next j
    Next i
    Sheet2.Range("B2:J" & lr) = var
MsgBox Timer - t
End Sub

Instead of Using Vlookup simple use Lookup Formula as below to get last matched value...

=LOOKUP(2,1/($H$2:$H$13=H2),J2:J13)

Thanks,
Nitesh

 

[milan]

Sir,
Above mentioned code of Vlookup returns with 1st match value
I need last match
Simple way explaining I want use below mentioned formula in VBA

=LOOKUP(2,1/($H$2:$H$13=H2),J2:J13)

 

[NARAYANK991]

Hi ,

I am afraid I cannot understand.

On the tab named Results , you have row labels and column headers ; the row labels repeat the 6 values listed in the tab named Data 1000 times.

The column headers in the tab named Results , are the same as the headers in the tab named Data , but in a different order.

Running the macro named LookMeUp populates the 6000 rows with data.

What the relationship of this with your problem is difficult to understand ; can you explain ?

Narayan

 

[milan]

Sir,
I have uploaded new workbook

• 2 Sheets are there
• One of them Named Data & 2nd one is named Result

In Sheet "Result" Column "B To E" i have entered formula expl=LOOKUP(2,1/(Data!$A:$A=Result!A2),Data!B:B)

I want write Vba Code For The same

 

[Nitesh Khot]

Please check if this Helps to you....

Kindly make changes as per your requirement...

Sub Macro2()

With Range("D2:D10")

    .Formula = "=LOOKUP(2,1/(Data!C1=Result!RC[-3]),Data!C)"

End With

End Sub