# Index & Match in VBA

#### bhalbach

##### Board Regular
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
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.
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### offthelip

##### Well-known Member
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``

Replies
0
Views
108
Replies
1
Views
133
Replies
3
Views
141
Replies
0
Views
36
Replies
0
Views
119

1,133,619
Messages
5,659,917
Members
418,536
Latest member
Tezzies

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

### Which adblocker are you using?

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

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