Hi,
I have 2 sheets in my workbook with codes in them. One is a master code list that has codes and their respective stock values, and I need to pull the values for certain codes onto my other sheet. It would work fine with a standard vlookup BUT....
The master code list has slight variations in the codes that I need to ignore in the vlookup.
For example, master list has a code 11112211 but my list has the code 11113311 - these both refer to the same item so I need the stock value for it. The numbers are all different every time, but it's always the 5th and 6th characters that need igoring.
I need the formula to look at the first four AND last two characters, ignoring the middle two but ONLY if the code is 8 digits long. I have no idea what the best way to build this is and I've never used INDEX / MATCH functions but am willing to do so if it's the best way to achieve the results I need.
I did consider having a couple of helper columns where I copy the first four and last two digits and then combine them and use this for the lookup, but I'd have to do that on my master list and the list I need to populate and I think it'll get really messy - I have about 3000 rows of data.
I'm hoping an expert here can help me!!
L
Bookmarks