# Index Match Function to VBA



## nhinx (Dec 20, 2022)

Hello,



Would like to ask your help to convert the below function to VBA.



=INDEX('Sheet 1'!A:A,MATCH(1,('Sheet 1'!B1:B90200='Sheet 2'!B3)*('Sheet 1'!C1:C90200='Sheet 2'!C3)*('Sheet 1'!D1:D90200='Sheet 2'!D3)*('Sheet 1'!E1:E90200='Sheet 2'!E3),0))

=INDEX('Sheet 1'!A:A,MATCH(1,('Sheet 1'!B1:B90200='Sheet 2'!B4)*('Sheet 1'!C1:C90200='Sheet 2'!C4)*('Sheet 1'!D1:D90200='Sheet 2'!D4)*('Sheet 1'!E1:E90200='Sheet 2'!E4),0))

=INDEX('Sheet 1'!A:A,MATCH(1,('Sheet 1'!B1:B90200='Sheet 2'!B5)*('Sheet 1'!C1:C90200='Sheet 2'!C5)*('Sheet 1'!D1:D90200='Sheet 2'!D5)*('Sheet 1'!E1:E90200='Sheet 2'!E5),0))



which will populate up to rows 90,200



Thank you


----------



## iggydarsa (Dec 20, 2022)

```
Sub IndexMatch()
    For i = 3 To 90200
        Sheets("Destination").Range("A" & i).Formula = "=INDEX('Sheet 1'!A:A,MATCH(1,('Sheet 1'!B1:B90200='Sheet 2'!B" & i & ")*('Sheet 1'!C1:C90200='Sheet 2'!C" & i & ")*('Sheet 1'!D1:D90200='Sheet 2'!D" & i & ")*('Sheet 1'!E1:E90200='Sheet 2'!E" & i & "),0))"
    Next i
End Sub
```


----------



## Fluff (Dec 20, 2022)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------



## nhinx (Dec 20, 2022)

iggydarsa said:


> ```
> Sub IndexMatch()
> For i = 3 To 90200
> Sheets("Destination").Range("A" & i).Formula = "=INDEX('Sheet 1'!A:A,MATCH(1,('Sheet 1'!B1:B90200='Sheet 2'!B" & i & ")*('Sheet 1'!C1:C90200='Sheet 2'!C" & i & ")*('Sheet 1'!D1:D90200='Sheet 2'!D" & i & ")*('Sheet 1'!E1:E90200='Sheet 2'!E" & i & "),0))"
> ...


Thank you iggydarsa for the code. However, the value came out #N/A. How will I activate the formula without doing the CTRL+SHIFT+ENTER?


----------



## nhinx (Dec 20, 2022)

Fluff said:


> What version of Excel are you using?
> 
> I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Thanks for the info.


----------



## nhinx (Dec 20, 2022)

nhinx said:


> Thank you iggydarsa for the code. However, the value came out #N/A. How will I activate the formula without doing the CTRL+SHIFT+ENTER?


I got it already. I just changed the word Formula to FormulaArray


----------

