Index/Match across workbooks using VBA issues

[Msears]

Hello All, I am trying to convert an array formula (see below) that works fine, but arrays won't work on workbooks that are shared. So I was trying to convert the array into VBA with a button, in hoping it will resolve my issue, but it needs some tweaking. Basically, I want it to index Col M on the Master Referral List 2021 workbook. Match names in Col A in Sheet A, with names in Col A in Master Referral List 2021 workbook, if they match, enter text that is in Col G on Master Referral List 2021 workbook, into Col K. This action needs to continue until it has matched all of Col A. All data starts on Row 2. Thanks in advance!
Original working array
{=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2='G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))}

VBA in Progress

Private Sub CommandButton3_Click()
Dim lastRow As Long
Application.ScreenUpdating = False

With Sheets("A")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("K2:K" & lastRow)
.Formula = "=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2,SEARCH(""/"",A2)+1)),'G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))"
.Value = .Value
End With

End With

Application.ScreenUpdating = True
End Sub

 

[bebo021999]

What the below INDEX(MATCH in your formula trying is searching for first match first occurence of value in cell A2 in column G in other WB, then returning corresponding value in column M:
=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2='G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))
Why not using VLOOKUP, a non array formula?
=VLOOKUP(A2,'G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$M$300,7,0)

Alternative way, you could use Range.Find method like this:

Dim f
Dim rng as range
set rng = < try to set rng as range G2:G300 in that WB>
For i =1 to lastRow
    f = rng.find (range("A" & i)
    If f is nothing then
        Range("K" & i).value = f.offset(,6).value
     end if
Next i

 

[Msears]

What the below INDEX(MATCH in your formula trying is searching for first match first occurence of value in cell A2 in column G in other WB, then returning corresponding value in column M:
=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2='G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))
Why not using VLOOKUP, a non array formula?
=VLOOKUP(A2,'G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$M$300,7,0)

Alternative way, you could use Range.Find method like this:

Dim f
Dim rng as range
set rng = < try to set rng as range G2:G300 in that WB>
For i =1 to lastRow
    f = rng.find (range("A" & i)
    If f is nothing then
        Range("K" & i).value = f.offset(,6).value
     end if
Next i

Thanks so much. I don't know why sometimes I choose to make things more difficult then they should be. The VLOOKUP works great.