Index Match Function to VBA

[nhinx]

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]

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]

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]

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

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]

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]

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