Index/Match across workbooks using VBA issues

Msears

Board Regular
Joined
Apr 14, 2022
Messages
56
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:
VBA Code:
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
 
Upvote 0
Solution
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:
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top