Index & Match in VBA

[bhalbach]

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]

 .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:

 .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?

 

[offthelip]

the test in the code is for an exact match and is done on this line in the 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:

If UCASE(inarr(i, 1)) = UCASE(datar(j, 1)) Then ' check for a match