retired_corriestout

I currently have a file at work that lists every single part that my company has sold since April. Some of these parts are simply manufactured and then sold while some of them have sub assemblies which are then all put together and then sold.

I have another file which lists each part and its sub assemblies in another long list. If I use VLOOKUP I can find the first sub assembly for each part and place it in the initial list. But I do not know a way to then find a second sub assembly on the list, since VLOOKUP always returns the first match it finds.

Anyway to ignore the first one to get the second one and to then ignore the first and second one to get the third one? Some of them have 12 sub assemblies!

retired_rubbia

It can be done, but the formula ill have to depend on how you have the spreadsheet set up for the 'sub assemblies'.

I use often a formula like this:

=IF(ISNA(VLOOKUP($J18,$J$26:$P$30,5,FALSE))=TRUE," 0",(VLOOKUP($J18,$J$26:$P$30,5,FALSE)))

The 5 represents the number column in the range where the result will be returned. Can be used if you just have the items listen by using an Hlookup instead.

retired_pmetzger1

Either pivot your data and use vlookup with the appropriate number of columns, or use offset and match, adjusting your offset location in realtion to where the item was previously found.