Lookup with offset (I think)

[Ahhhh]

Hello All
So, I have a column with sequential numbers, every so often there is a word. For an example I will say after every 5 numbers the word stop appears. So something like
1, 2, 3, 4, 5, stop, 6, 7, 8, 9, 10, stop, 11, 12, 13.... I am trying to figure out a way in VBA to lookup a specified number that will give me the result of the number previous to the closest "stop"
Example 1 gives 5 as well as 2, 3, 4, 5. Then 6 would give 10 as well *** 7, 8, 9, 10. and so on. I hope that I explained this ok. Any help would be appreciated.
Thanks

 

[iggydarsa]

Lets say you type in the number you are looking for in A1 and the list is under column C

=INDIRECT(ADDRESS(MATCH("STOP", INDIRECT(ADDRESS(MATCH(A1,C:C, 0), 3) & ":" & ADDRESS(ROWS(C:C), 3)), 0)+MATCH(A1,C:C, 0)-2, 3))

In the formula, the number 3 represents column C, you might want to change it based on your need.

 

[Ahhhh]

Lets say you type in the number you are looking for in A1 and the list is under column C

=INDIRECT(ADDRESS(MATCH("STOP", INDIRECT(ADDRESS(MATCH(A1,C:C, 0), 3) & ":" & ADDRESS(ROWS(C:C), 3)), 0)+MATCH(A1,C:C, 0)-2, 3))

In the formula, the number 3 represents column C, you might want to change it based on your need.

Wow, thanks for the quick reply, It worked great. Is there a way to tweak it. "STOP" appears in A1 occasionally and it gets a #VALUE! error.
Thanks again for the help.

 

[Ahhhh]

Opps, One more thing
In a cell it worked like magic, I just tried putting it into VBA

Range ("F3")=INDIRECT(ADDRESS(MATCH("BR", INDIRECT(ADDRESS(MATCH(C1,C:C, 0), 3) & ":" & ADDRESS(ROWS(C:C), 3)), 0)+MATCH(C1,C:C, 0)-2, 3))

and I'm getting a error, any Ideas?
Thanks

 

[iggydarsa]

Range ("F3").Formula= "=INDIRECT(ADDRESS(MATCH(""BR"", INDIRECT(ADDRESS(MATCH(C1,C:C, 0), 3) & "":"" & ADDRESS(ROWS(C:C), 3)), 0)+MATCH(C1,C:C, 0)-2, 3))"

 

[Ahhhh]

Hello Iggy
Thanks for the help with this.
So the code is now working in VBA but it is only finding the first BR.
I truly appreciate the help
Thanks

 

[iggydarsa]

It is really hard to visualize what you need without seeing the data.
Can you post it here?

 

[Ahhhh]

Ok

https://app.box.com/s/my8kjtxbjzrq4wvhaw9v1mxigsxuxls2

[COLOR=#333333]Range ("F3").Formula= "=INDIRECT(ADDRESS(MATCH(""BR"", INDIRECT(ADDRESS(MATCH(C1,C:C, 0), 3) & "":"" & ADDRESS(ROWS(C:C), 3)), 0)+MATCH(C1,C:C, 0)-2, 3))"[/COLOR]

Also, C1 will sometimes be "BR" and give an error

 

[Ahhhh]

Ok

https://app.box.com/s/my8kjtxbjzrq4wvhaw9v1mxigsxuxls2

[COLOR=#333333]Range ("F3").Formula= "=INDIRECT(ADDRESS(MATCH(""BR"", INDIRECT(ADDRESS(MATCH(C1,C:C, 0), 3) & "":"" & ADDRESS(ROWS(C:C), 3)), 0)+MATCH(C1,C:C, 0)-2, 3))"[/COLOR]

Also, C1 will sometimes be "BR" and give an error

Oh, so I just figured out the issue, It's using C1 in the search so c:c is using it. I switched the range to C2:C25 and it works except if BR is the search and the number following the BR (7, 11, 15)

 

[Ahhhh]

Hello Iggy,
I was wondering if you had any thought on the number following the BR (7, 11, 15), they all go to the number prior to the BR. I've been trying different things none seem to work.
Any Ideas would be appreciated.
Thanks for your help.