HELP...not sure how to do this

[TEKMSTR]

Hello, all my efforts have failed any ideas would be greatly appreciated.

here's an example:

...A.............B
Parts.........IND
0001...........1
0002...........2
0003...........2
0004...........2
0005...........3
0006...........3
0007...........4
0008...........3
0009...........3
0010...........2

Here's how to interpret this. All parts with an IND's 2 are components found on IND 1. All IND's 3 are found on IND 2....and so on. The trick to this is for example all IND's 3....I only need the last IND 2 that is directly above it.

Here is the result:
...A.............B............C
Parts.........IND........RES
0001...........1...........0001 (IND's of 1 belong to themselves)
0002...........2...........0001
0003...........2...........0001
0004...........2...........0001
0005...........3...........0004 (only need the part # for last listed IND)
0006...........3...........0004
0007...........4...........0006
0008...........3...........0004
0009...........3...........0004
0010...........2...........0001

Each part only belongs to the last listed one above it with a lower IND. I've tried various look up formulas to no avail.

I'm thinking this might call for some kind of offset formula but I cant get it to work.

Be advised this is a small sample. In the list there can be 100's of part number with for example IND 3, so a nested IF statement that I've also tried would not work.

Thanks for any ideas.

 

[Domenic]

Try...

C2, copied down:

=IF(B2>1,LOOKUP(2,1/(B$2:B2=B2-1),A$2:A2),A2)

Hope this helps!