Index & Match in VBA

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
How do I convert the following formula to index match in VBA? I cannot wrap my head around this...I am trying to learn but im struggling.

=INDEX(rngLabourRates,MATCH(D13,rngLabour),0)

I have made the following vlookup code work to accomplish what i want but it returns an error when there is a blank in D13:D27 range of TskSheet.
Sub VlookupLabourRate()

For i = 13 To 27
Worksheets("TskSheet").Cells(i, 8).Value = Application.WorksheetFunction.VLookup(Worksheets("TskSheet").Cells(i, 4).Value, Worksheets("Labour").Range("D:P"), 13, 0)
Next

End Sub

I think that Index Match is what I should be using but I am open to any suggestions on how to do this so it returns my results very quickly. Should I be considering doing this with an array? Speed is important.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
906
Office Version
  1. 2010
Platform
  1. Windows
Never ever use WorksheetFunction except if you want to crash your VBA procedure when the function raises an error like MATCH, VLOOKUP, whatever !​
Just use Application rather than WorksheetFunction …​
Use a Variant variable to store the function result like V = Application.Match(… and check it via VBA functions IsError or IsNumeric for example.​
As worksheet functions can be used via Evaluate - a VBA help must see - like [INDEX(rngLabourRates,MATCH(D13,rngLabour),0)] …​
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
Never ever use WorksheetFunction except if you want to crash your VBA procedure when the function raises an error like MATCH, VLOOKUP, whatever !​
Just use Application rather than WorksheetFunction …​
Use a Variant variable to store the function result like V = Application.Match(… and check it via VBA functions IsError or IsNumeric for example.​
As worksheet functions can be used via Evaluate - a VBA help must see - like [INDEX(rngLabourRates,MATCH(D13,rngLabour),0)] …​
So is the code for the vlookup the same...just replace the Application.WorksheetFunction?

I am really new to this and trying to figure it. What would the code look like?
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
So is the code for the vlookup the same...just replace the Application.WorksheetFunction?

I am really new to this and trying to figure it. What would the code look like?
I got this to work but it returns a #N/A when there is a blank.

What would the entire code look like to avoid this. Sorry I don.t just want you to provide code...I am just very new to this and trying to learn.
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This works but I get a #N/A if it encounters a blank in column D

Sub VlookupLabourRate()


For i = 13 To 27
Worksheets("TskSheet").Cells(i, 8).Value = Application.VLookup(Worksheets("TskSheet").Cells(i, 4).Value, Worksheets("Labour").Range("D:P"), 13, 0)
Next

End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
906
Office Version
  1. 2010
Platform
  1. Windows
Yes the same error using the same function in a cell formula when nothing matches ! Try …​
So just check before your column D or as I yet explained in post #2 how to check the result when using Application rather than WorksheetFunction …​
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,845
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

The way you are trying to use worksheet functions to do lookups in VBA is not a very efficient use of VBA. Accessing the worksheet from vba takes relatively long time. Accessing one single cell takes about as long as accessing a whole range, your code makes three accesses to worksheet on every loop. Since your loop in only a 14 rows this is probably not important, but if it was a few thousand rows your code would be very slow. You said you wanted the results returned quickly. Using variant array is usually about 1000 times faster than the code you are writing. I have written some vba code to do the same task using variant arrays. I am also using the With End With construct which also should speed up thing a little bit:
I have annotated to code to hel;p you understand what it does:
VBA Code:
Sub test2()
With Worksheets("Labour")
lastrow = .Cells(Rows.Count, "D").End(xlUp).Row ' find the last row on labour sheet
datar = .Range(.Cells(1, 4), .Cells(lastrow, 16)) ' pick all the data in columns D to P  from row 1 to the last row
End With


With Worksheets("TskSheet")
inarr = .Range(.Cells(13, 4), .Cells(27, 4)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 8)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   For j = 1 To lastrow        ' loop through each row of the Labour data to find a match
    If inarr(i, 1) = datar(j, 1) Then ' check for a match
     outarr(i, 1) = datar(j, 13)      ' copy data to output when matched
     Exit For                         ' exit the inner loop because we have matched the look up
    End If
   Next j
Next i
.Range(.Cells(13, 8), .Cells(27, 8)) = outarr ' write output array back to the workhseet
End With
End Sub
PS this also has the advantage you can deal with any "errors" yourself without causing a crash
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
906
Office Version
  1. 2010
Platform
  1. Windows
Yes, or just using directly formulas without looping …​
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
The way you are trying to use worksheet functions to do lookups in VBA is not a very efficient use of VBA. Accessing the worksheet from vba takes relatively long time. Accessing one single cell takes about as long as accessing a whole range, your code makes three accesses to worksheet on every loop. Since your loop in only a 14 rows this is probably not important, but if it was a few thousand rows your code would be very slow. You said you wanted the results returned quickly. Using variant array is usually about 1000 times faster than the code you are writing. I have written some vba code to do the same task using variant arrays. I am also using the With End With construct which also should speed up thing a little bit:
I have annotated to code to hel;p you understand what it does:
VBA Code:
Sub test2()
With Worksheets("Labour")
lastrow = .Cells(Rows.Count, "D").End(xlUp).Row ' find the last row on labour sheet
datar = .Range(.Cells(1, 4), .Cells(lastrow, 16)) ' pick all the data in columns D to P  from row 1 to the last row
End With


With Worksheets("TskSheet")
inarr = .Range(.Cells(13, 4), .Cells(27, 4)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 8)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   For j = 1 To lastrow        ' loop through each row of the Labour data to find a match
    If inarr(i, 1) = datar(j, 1) Then ' check for a match
     outarr(i, 1) = datar(j, 13)      ' copy data to output when matched
     Exit For                         ' exit the inner loop because we have matched the look up
    End If
   Next j
Next i
.Range(.Cells(13, 8), .Cells(27, 8)) = outarr ' write output array back to the workhseet
End With
End Sub
PS this also has the advantage you can deal with any "errors" yourself without causing a crash
This works awesome, I can not see any delay like I did with the code I was using. Thank you for your

I presume this code could also do a calculation at the same time?
While running the lookup and match can it multiply the resultant value that goes in column H x column E & F & G? So....the value that is found in column H = the returned value * E * F * G. Then I can eliminate a formula.

I really appreciate you help.
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
The way you are trying to use worksheet functions to do lookups in VBA is not a very efficient use of VBA. Accessing the worksheet from vba takes relatively long time. Accessing one single cell takes about as long as accessing a whole range, your code makes three accesses to worksheet on every loop. Since your loop in only a 14 rows this is probably not important, but if it was a few thousand rows your code would be very slow. You said you wanted the results returned quickly. Using variant array is usually about 1000 times faster than the code you are writing. I have written some vba code to do the same task using variant arrays. I am also using the With End With construct which also should speed up thing a little bit:
I have annotated to code to hel;p you understand what it does:
VBA Code:
Sub test2()
With Worksheets("Labour")
lastrow = .Cells(Rows.Count, "D").End(xlUp).Row ' find the last row on labour sheet
datar = .Range(.Cells(1, 4), .Cells(lastrow, 16)) ' pick all the data in columns D to P  from row 1 to the last row
End With


With Worksheets("TskSheet")
inarr = .Range(.Cells(13, 4), .Cells(27, 4)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 8)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   For j = 1 To lastrow        ' loop through each row of the Labour data to find a match
    If inarr(i, 1) = datar(j, 1) Then ' check for a match
     outarr(i, 1) = datar(j, 13)      ' copy data to output when matched
     Exit For                         ' exit the inner loop because we have matched the look up
    End If
   Next j
Next i
.Range(.Cells(13, 8), .Cells(27, 8)) = outarr ' write output array back to the workhseet
End With
End Sub
PS this also has the advantage you can deal with any "errors" yourself without causing a crash
Sorry I told you the calculations I needed wrong.

I need it to place the resultant just like you have it...then calculate that resultant *E*F*G and put this result in "I".

Could you assist me with the code on this last thing?

I would really appreciate it...I have 9 different macros that I need that are very similar to this one and I could modify your code then.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,841
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top