Index Match Vlook up for a master file

rsernowski
rsernowski used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

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?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Author

Commented:
Subodh, thanks for your help. Appreciate your time
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad to help.
Sorry I didn't get time to reply.

Author

Commented:
Hi

No problem

I will work on the solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial