# Problem with Index Loop

#### AndyD

##### Active Member
Hi
We've thousands of records using the index look-up formula
I thought i could save time by using a loop without updating the screen....however this takes longer than copying an index formula down the range...any ideas as to why this might be?

Sub macro_1()

Application.ScreenUpdating = False

Dim i As Integer

For i = 6 To 25000

Cells(i, 3).Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R3C[2]:R65000C[3],MATCH(RC[-1],R3C[2]:R65000C[2],0),2)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Next i

End Sub

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### inarbeth

##### Well-known Member
Can't say why but I tried this macro for a lookup rather than index. I put the formula in cell B1 and then ran this macro:
Sub Macro1()

'
Selection.AutoFill Destination:=Columns("B:B"), Type:=xlFillDefault
Columns("B:B").Select

End Sub

This ran almost instantly.

#### AndyD

##### Active Member

quick question: does a lookup formula find a near match if cant find an exact match (as we need to always find exact match)

also we have up to 65,000 records to lookup/index about 40,000 records so can take a while...

thanks
Andy

#### WillR

##### Well-known Member
You can specify whether to find an exact match or the closest match

Placing FALSE in a VLOOKUP formula will specify an exact match - an error value will be returned if a match is not found...

similarly, placing a 0 (zero) in a MATCH formula will amount to the same...

See the excel help files for details of the various formula syntaxs

#### inarbeth

##### Well-known Member
An addendum. You need to reselect B1 before running the macro to avoid an error.

L

#### Legacy 21301

##### Guest
AndyD said:
Hi
We've thousands of records using the index look-up formula
I thought i could save time by using a loop without updating the screen....however this takes longer than copying an index formula down the range...any ideas as to why this might be?

Because you are selecting a cell and copying the formula 24,995 times, versus selecting and copying once.

In any event, macro or not, filling so many cells with an INDEX formula is not quick since INDEX is a volatile function and the formulas get re-calculated whenever the worbook is re-calculated.

As has been suggested, you could use VLOOKUP instead.
If you need a macro :-

Code:
``````Sub macro_1()
[C6:C25000].FormulaR1C1 = "=VLOOKUP(RC[-1],R3C[2]:R65000C[3],2,0)"
End Sub``````

Or, to restrict it to the used cells in column B ;-

Code:
``````Sub macro_1()
Range([C6], [B65536].End(xlUp)(1, 2)).FormulaR1C1 = "=VLOOKUP(RC[-1],R3C[2]:R65000C[3],2,0)"
End Sub``````

Replies
2
Views
89
Replies
1
Views
216
Replies
2
Views
165
Replies
1
Views
208
Replies
0
Views
202

1,186,165
Messages
5,956,330
Members
438,247
Latest member
UZev

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