# Lookup with offset (I think)

#### Ahhhh

##### New Member
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

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Lets say you type in the number you are looking for in A1 and the list is under column C

Code:
``=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.

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

Code:
``=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.

Opps, One more thing
In a cell it worked like magic, I just tried putting it into VBA
Code:
``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

Code:
``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))"``

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

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

Ok

https://app.box.com/s/my8kjtxbjzrq4wvhaw9v1mxigsxuxls2
Code:
``[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

Ok

https://app.box.com/s/my8kjtxbjzrq4wvhaw9v1mxigsxuxls2
Code:
``[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)

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.

Replies
16
Views
481
Replies
9
Views
224
Replies
3
Views
221
Replies
1
Views
155
Replies
3
Views
134

Threads
1,203,112
Messages
6,053,577
Members
444,674
Latest member
DWriter9

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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