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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Subodh, thanks for your help. Appreciate your time
You're welcome. Glad to help.
Sorry I didn't get time to reply.
Sorry I didn't get time to reply.
ASKER
Hi
No problem
I will work on the solution
No problem
I will work on the solution
In D2
Open in new window
In D3Open in new window
Same way you can create the formulas for other cells by changing the range reference within Match part.