Index Match Vlook up for a master file

Hi

I would like a formula for my vlookup in index match format, the formula is vlookup(c2,masterA5:E8,5, False) . I have attached a sample. So what I do is get the invoices from the vendor,  but the vendor doesn't have the same part code as me. So I do a vlookup against a master part code and then pivot the results so I can see qty of sales. The master contains my parts codes and their corresponding part codes

I would like to use Index match instead of vlookup becuase if I insert a col, my formulas stop working
Index_match-Sample.xlsx
rsernowskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try it like this.....
In D2
=INDEX(Master!$E$5:$E$8,MATCH(C2,Master!$A$5:$A$8,0))

Open in new window

In D3
=INDEX(Master!$E$5:$E$8,MATCH(C3,Master!$B$5:$B$8,0))

Open in new window

Same way you can create the formulas for other cells by changing the range reference within Match part.
rsernowskiAuthor Commented:
Hi Subodh,

thanks that is exactly what I require. After I entered the Index formula, I went to the master , inserted a COL and the Vlookup formulas gave a 0 , while the index formula still worked.

Do you have any VBA skills?
What you be able to show me the same formula in VBA format.  I have some VBA skills so I know I would declare my range  but I am wondering if I can name a range for each vendor?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you want to fetch the Group for each vendor on Sheet1 from the Master Sheet with the help of VBA, why would you insert formulas on Sheet1 through VBA? You can fetch the groups with the help of VBA procedure without inserting the formulas.
e.g. below code will find the group for each vendor from the Master Sheet.
In the attached, click the button "Find Vendor Group" to run the code.

Sub GetGroup()
Dim sws As Worksheet, dws As Worksheet
Dim dlr As Long, slr As Long, r As Long, col As Long, myCodeCol As Long
Dim rng As Range, cell As Range
Set sws = Sheets("Master")
Set dws = Sheets("Sheet1")
dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row
slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = dws.Range("A2:A" & dlr)
If Application.CountIf(sws.Rows(4), "MyCode") > 0 Then
myCodeCol = Application.Match("MyCode", sws.Rows(4), 0)
For Each cell In rng
    If Application.CountIf(sws.Rows(4), cell.Value) > 0 Then
        col = Application.Match(cell.Value, sws.Rows(4), 0)
        r = Application.Match(cell.Offset(0, 2).Value, sws.Columns(col), 0)
        If col > 0 Then
            cell.Offset(0, 3).Value = sws.Cells(r, myCodeCol)
        End If
    End If
Next cell
Else
    MsgBox "MyCode Header is not found in Row4 of Master Sheet.", vbExclamation, "Header Not Found!"
    Exit Sub
End If
End Sub

Open in new window

Index_match-Sample.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rsernowskiAuthor Commented:
Hi Subodh,

Sorry I didn't get back earlier. This is awesome. But it is a bit premature for my file structure. What would I need to do to the code if my sheet 1 , contained a date column in place of the varying vendors? At present I am getting a single file from each vendor .  I still have the same problem of matching their code to mine. I have attached a sample of what I mean
Thanks
rsernowskiAuthor Commented:
Subodh, thanks for your help. Appreciate your time
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
Sorry I didn't get time to reply.
rsernowskiAuthor Commented:
Hi

No problem

I will work on the solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.