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.
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
 .Range(.Cells(13, 4), .Cells(27, 7))
This is a method of addressing the worksheet which uses number for the rows and the columns. So cells(13,4) is the 13th row and the 4th column: ABCD thus this cell is row13 column 4 i.e D13.
Cells (27,7) is the cells that marks row 27 and column 7, ABCDEFG i.e G27, so this statement is exactly equivalent to:
VBA Code:
 .Range("D13:G27")
The main reason for using numbers instead of letters is that once you have loaded the worksheet into a variant array YOU HAVE to use numbers. So the addressing for inarr and outarr has to be using numbers. The array numbers start at the position the array is loaded from so the first elements of the inarr array is the value in D13, so
inarr (1,1) = cells("d13").value
inarr (2,1) =cells("D14").value
inarr(2,2)=cells("E14").value
etc , etc down to
inarr(inarr(15,4)= cells("G27").value
If you load an array starting at cell A1, then the numbers in the array tie up with the row and column numbers, i.e.
if we had loaded inarr like this:
inarr=range(cells(1,1),cells(27,7)) the array would have been larger and might have been a bit slower to load, but it would simpilfy the addressing
Eg. cells("d13").value=inarr(13,4)
So I try to do this if I know there is nothing in the extra areas I load into the array which could be destroyed when I write it back. i.e. a formula
The code is working nicely, but I have noticed something.

The lookup value on sheet TskSheet in range D13:D27 is a data validation list with text...when I select from the list everything works great. If I type the text in the data validation cell in lower case it seems to be case sensitive ie: Supervisor works if typed or selected from the data validation list...but if I type in supervisor (lower case S) it is not finding the match.

Any thoughts?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
the test in the code is for an exact match and is done on this line in the code:
VBA Code:
If inarr(i, 1) = datar(j, 1) Then ' check for a match
( as the comment states) if you want check for text match regardless of case you can force the comparison by forcing both strings to upper case using the VBa UCASE function:
VBA Code:
If UCASE(inarr(i, 1)) = UCASE(datar(j, 1)) Then ' check for a match
 

Watch MrExcel Video

Forum statistics

Threads
1,130,240
Messages
5,641,036
Members
417,189
Latest member
koelleyath

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