LOOKUP with Duplicate Values, so go to next instance

[Cat129]

I'm not too sure how to explain this one so will do my best. I have a set of data (parts lists) approx 4000 rows on a worksheet, this is the last weeks data. New data in generated each week, and we need to transfer certain data from last weeks data into this weeks. The new data that is generated goes into a new worksheet within columns A:V, and last weeks data that needs to be moved across is in rows W:BW. Columns A:V are also full of data which is what I can use for referencing

My first thought was to use a VLOOKUP or INDEX and MATCH, but I have data that is identical in rows A:V on this weeks data and last weeks data, but the information from W:BW is different.

So for example we may use the same bushing in multiple assemblies (data shown in columns A:V) but the added information in W:BW may be different, because of different suppliers / manufacturing processes.

Using one of the lookup methods above will only return the top value is finds in last weeks data (columns A:V), what I want it to do, it understand it has already found this value once and then continue to look down last weeks data to find the next instance of this within A:V, and copy the data from W:BW into this weeks data set.

Or to look at the rows above (which I dont think is possible) to see its assembly number and match the rows up that way

Can anyone help me?

Thanks,
Cat

 

[sheetspread]

Then subsearch by row # (either relative or absolute), no?

If that sounds unclear, post a worst case example with duplicate parts per group and desired output, I'll match it formulaically