get value reffering end of another similar column

[arctushar]

I need simpler formula where user will input ID number from 3rd column and column 4-6 will be auto. example file has been attached. Where green is input value and Red is formulated value. is it possible? plz help me.

column header is 1.ID 2. Line 3.This line end ID 4. Next Line start ID 5. Next line end ID

ID Line ID This line end ID Next Line start ID Next Line end ID
21 1 22 25 26 32
22 1
23 1
24 1
25 1
26 2
27 2
28 2
29 2
30 2
31 2
32 2
33 3
34 3
35 3
36 3
37 3
38 3
39 3
new formula.xls this is an example file.

[ben_hensel]

Try this on for size. I ended up creating a helper column in G, but if you don't mind increasing your lookups you could just replace the references to G2 with the contents of G2.

MY basic method was, taking advantage of the approximate matching of the INDEX function. Therefore, this requires that the numbers in columns A & B be sorted in ascending order.

C2: ID
D2: This line end ID
E2: Next line start ID
F2: Next line end ID
G2: This line array element #

Please Login or Register  to view this content.

I would have to look at it more to make sure it's optimal, but since they're all approximate searches, it's not like this is all that resource-intensive.

[Ace_XL]

Use the below

In D2
=IFERROR(INDEX($A$2:$A$20,MATCH(OFFSET(INDEX($B$2:$B$20,MATCH(VLOOKUP(C2,A:B,2,FALSE),$B$2:$B$20,0)),COUNTIF($B$2:$B$20,VLOOKUP(C2,A:B,2,FALSE)),0),$B$2:$B$20,0)-1,0),"")

In E2
=D2+1

In F2
=IFERROR(INDEX($A$2:$A$20,MATCH(OFFSET(INDEX($B$2:$B$20,MATCH(VLOOKUP(E2,A:B,2,FALSE),$B$2:$B$20,0)),COUNTIF($B$2:$B$20,VLOOKUP(E2,A:B,2,FALSE)),0),$B$2:$B$20,0)-1,0),"")


Forumla in F2 is same as in D2, but looking up value in E2 instead