How to change VBA Vlookup into a Index/Match

[puupanda]

Hi all! I have a macro that fills a sheet automatically using .Cells().Value=Application.Vlookup()....

Basically all I want to do is change the Vlookup to Index(Match()). I know the first thing is to call the index function with Application.WorksheetFunction.Index() etc..

I have a workbook with a Sheet called "Avauspohja" where I have the macro filling data. In "Avauspohja" the lookup_value is in column "M"
I also have a Sheet called "SyndicationData" which includes the raw data. In "SyndicationData" the vlookup_value is in column "D" (and all the data im searching for is to the right, I used max range of "D:XXX")


I want to use Index match because the number of columns in "SyndicationData" is not constant (Example below)

THE PROBLEM is that I receive empty results. No errors. Just blank.


Old Code with Vlookup:

.Cells(i, "AA").Value = Application.VLookup(.Cells(i, "M").Value, Worksheets("SyndicationData").Range("D:M"), 10, 0)

New Code with Index(match()):

.Cells(i, "AA").Value = Application.WorksheetFunction.Index(Sheets("SyndicationData").Range("D:XXX"), Application.WorksheetFunction.Match(.Cells(i, "M").Value, _
            Sheets("SyndicationData").Range("D:D"), 0), Application.WorksheetFunction.Match("Customs Tariff Number (CustomsTariffNumber)", Sheets("SyndicationData").Range("1:5"), 0))

I made the row match look in range "D:D" because the lookup_value is in column "D"
I made the column match look in range "1:5" because the headers for the raw data are in rows 2-3

Note 1: The data im trying to return in vlookup code is a Customs Tariff Number which usually is located in column "M" in "SyndicationData" sheet but it can also be e.g. in column "P".
Note 2: The code example is a part of a loop hence the i-values.


Why does my index match result in blank cell?

If you need more information I'm more than happy to provide.

Cheers,
puupanda

 

[steve the fish]

Hi. That has to fail. There must be a error handler within the vba? You cant have Range("D:XXX"). You also cant use multiple rows/ columns with match such as Application.WorksheetFunction.Match("Customs Tariff Number (CustomsTariffNumber)", Sheets("SyndicationData").Range("1:5"), 0). You need to fix your index range and also know the row for the match.

 

[puupanda]

Hi steve,

Yes there is an errorhandler. Is that what is giving me the blanks instead of error. Sweet! (Y)

So I can't just use Index() Or Match() functions in vba like they are used as functions on "normal" worksheets?

You need to fix your index range and also know the row for the match.

Meaning what? I Have to have e.g. "D1:XXX999" ?

Br,
puupanda

 

[steve the fish]

There is no such column as XXX.

Lets start from the start are you basically saying 'Customs Tariff Number (CustomsTariffNumber)' is always in the headers but not always in the same column? Which row are the headers in?

 

[steve the fish]

I finish for the weekend soon so if nobody else helps maybe I can on Monday.

 

[puupanda]

Which row are the headers in?

The headers are in row 2 in sheet "SyndicationData"

And yes they are not always in the same column which is why I can't use the vlookup.

 

[puupanda]

Out of interest.

Is it possible to do a Match() inside the Application.Vlookup() instead of inserting the number that represents the column distance?

e.g.

.Cells(i, "AA").Value = Application.VLookup(.Cells(i, "M").Value, Worksheets("SyndicationData").Range("D:M"), [B][U]Application.WorksheetFunction.Match("Header1", Worksheets("SyndicationData").Range("D2:ZZ2"),0)[/U][/B], 0)

 

[JackDanIce]

Why not write your own function instead of using native Excel ones?

Public Function myVlookup(ByRef LookupItem As Variant, ByRef KeyRange As Range, ByRef LookupRange As Range) As Variant
 
    ' LookupItem is a single object
    ' KeyRange and LookUp Range are single column ranges with matching row count

    Dim Dic         As Object
    
    Dim arrKey()    As Variant
    Dim arrLookup() As Variant
    
    Dim x  As Long

    arrKey = KeyRange.value
    arrLookup = LookupRange.value
    
    If UBound(arrKey, 1) <> UBound(arrLookup, 1) Then
        MsgBox "Invalid arguments passed to UDF", vbExclamation, "Invalid Arguments"
        Exit Function
    Else
        For x = LBound(arrKey, 1) To UBound(arrKey, 1)
            Dic(arrKey(x, 1)) = arrLookup(x, 1)
        Next x
    End If
        
    myVlookup = IIf(Dic.exists(LookupItem), Dic(LookupItem), CVErr(xlErrValue))
 
End Function

Which is agnostic to whether the lookup column is left or right of the column to return. Then you can use it as:

With Sheets("SyndicationData")
    Set KeyRange = .Range("D:D")
    Set MatchRange = .Range("D2:ZZ2").find(what:="Header1", LookIn:=xlValues, lookat:=xlWhole).EntireColumn
End With

.Cells(i, "AA").value = myVlookup(.Cells(i, "M").value, KeyRange, MatchRange)

 

[puupanda]

Thank you JackDanIce! I finally figured everything out and you were right. The easiest way was to come up with UDF instead of using native excel functions. This actually helps my future work too.

Cheers!