# HELP...not sure how to do this

#### TEKMSTR

##### Board Regular
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try...

C2, copied down:

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

Hope this helps!

Replies
8
Views
520
Replies
9
Views
563
Replies
1
Views
225
Replies
5
Views
252
Replies
4
Views
194

1,218,899
Messages
6,145,094
Members
450,590
Latest member
Naneng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back