How to do a Vlookup/Index-Match?

Question

I get 1004 error when trying to do a Vlookup/Index-Match, or no results at all.

I tried both vlookup and Index /match inside a While loop.

I have used basically the same loop in many of my macros without any issues so far. I tried adjusting the formats (even with Format painter).

I isolated the values for the lookup in a new file and did just the lookup part.

Note: A manual Vlookup or index.Match works.

Code:

Sub lookup()

 'Variables for the Vlookups
 Dim newRow As Long
 Dim newCL As String

 newRow = 7

 Dim rn As Range
 Set rn = Worksheets("trysheet").Range("C:C")
 Dim mrange As Range
 Set mrange = Worksheets("trysheet").Range("A:A")

 On Error Resume Next
 'lookups
 Do While Worksheets("test").Cells(newRow, 4).Value <> ""
 newCL = Worksheets("test").Cells(newRow, 4).Value
 Worksheets("test").Cells(newRow, 5) = Application.Index(rn, 
 Application.Match(newCL, mrange, 0))
 newRow = newRow + 1
 Loop

End Sub

I have tried using Application.WorksheetFunction instead of just Application. but then I either receive

1004 error - "Unable to get Match/Vlookup property of the WorksheetFunction class"

or

Application defined error

EDIT:
I achieved the required result using the traditional vlookup function (with the help of macro recording and modifying my loop):

Sub lookup()

'Variables for the Vlookups
Dim newRow As Long

newRow = 7

Dim rn As Range
Set rn = Worksheets("trysheet").Range("C:C")
Dim mrange As Range
Set mrange = Worksheets("trysheet").Range("A:A")

On Error Resume Next
'
'Get Employee
Do While Worksheets("test").Cells(newRow, 4).Value <> ""
 Worksheets("test").Cells(newRow, 5).FormulaR1C1 = "=VLOOKUP(RC[-1], trysheet!C[-4]:C[-2],3,0)"
 newRow = newRow + 1
Loop

End Sub

I usually try to avoid this method and stick to the application.WorksheetFunction or application.Vlookup(example) because I find it more clean and automatically removes the formula, but in this case does the job.

Still help me understand why the application method did not work here, because this happens to me for the first time.

yytsunamiyy had some good suggestions below, but still not complete.

Hi Nathan, yes, newCL is in mrange, in fact in my case all values which newCL will take will be matched... — M_Delineshev

yytsunamiyy yytsunamiyy · Accepted Answer · 2019-01-04T16:25:37

Your Index formula is missing a column reference, try:

Worksheets("test").Cells(newRow, 5) = Application.Index(rn, Application.Match(newCL, mrange, 0),1)

following the discussion below here my solution:

It seems that your sheet references and therefor your ranges are incorrect. Best practice is to use the codenames of sheets rather than the display names to reference sheets. Here an example of how to do this and how you would use it in your procedure:

Public shTest As Worksheet
Public shTrySheet As Worksheet

Sub run_lookups()

    Call defineVars
    Call lookup_newrow

End Sub

Sub defineVars()

    Dim sh As Worksheet

        For Each sh In ThisWorkbook.Sheets
            Select Case sh.CodeName
                Case "Test"
                    Set shTest = sh
                Case "TrySheet"
                    Set shTrySheet = sh
            End Select
        Next sh

End Sub

Sub lookup_newrow()

    'Variables for the Vlookups
     Dim newRow As Long
     Dim newCL As String

        newRow = 7

    Dim rn As Range
        Set rn = shTrySheet.Range("C:C")
    Dim mrange As Range
        Set mrange = shTrySheet.Range("A:A")

 'On Error Resume Next
 'lookups
    Do While shTest.Cells(newRow, 4).Value <> ""
        newCL = shTest.Cells(newRow, 4).Value
        shTest.Cells(newRow, 5).Value = Application.WorksheetFunction.Index(rn, Application.Match(newCL, mrange, 0), 1)
        newRow = newRow + 1
    Loop

End Sub

Even more elegant is the solution to simply write the proper formula in R1C1 Format into the range. If you need fixed values simply do a copy and pastespecial.

Public shTest As Worksheet
Public shTrySheet As Worksheet

Sub run_lookups2()

    Call defineVars2
    Call lookup_values

End Sub

Sub defineVars2()

    Dim sh As Worksheet

        For Each sh In ThisWorkbook.Sheets
            Select Case sh.CodeName
                Case "Test"
                    Set shTest = sh
                Case "TrySheet"
                    Set shTrySheet = sh
            End Select
        Next sh

End Sub

Sub lookup_values()
   Dim rLookupResults As Range
   Dim sName As String

    sName = shTrySheet.Name 'Sheet is referenced through codename, which is unlikely to get changed, but for fomula displayed sheetname is used

    With shTest

        Set rLookupResults = .Range(.Cells(7, 5), .Cells(.Cells(.Rows.Count, 4).End(xlUp).Row, 5))

        'enter LOOKUP() Formula in Lookup Rsult Rang
        rLookupResults.FormulaR1C1 = "=IFERROR(LOOKUP(RC[-1]," & sName & "!C[-4]," & sName & "!C[-2]),"""")"

        'if fixed values are need copy and pastespecial
            rLookupResults.Copy
            rLookupResults.PasteSpecial xlPasteValues
    End With

End Sub

How to do a Vlookup/Index-Match?

2 Answers