Lookup cell address of cell containing specific word, then next occurrence and so on.

AnyaK

New Member
Joined
Jun 5, 2017
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm using CELL with INDEX/MATCH to find the next occurrence of the word "end" in a column. The problem is it only returns the address of the first occurrence and that's it.

How can I find the address the next occurrence of the word "end", then the next and so on?

Any help much appreciated as I'm not coping. (I have autism and learning difficulties so get upset when I feel stupid. My apologies.)

Thank you,

AnyaK
 

Attachments

  • Screenshot 2024-01-17 113637.png
    Screenshot 2024-01-17 113637.png
    50.1 KB · Views: 13

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You could try something like below. However, I am wondering what you are going to do with this address next?
For most questions like this the next step can usually be completed (more efficiently) without the need to actually calculate this address.

24 01 17.xlsm
MNO
1
2$M$8
3$M$8
4$M$8
5$M$8
6$M$8
7$M$8
8end$M$8
9$M$15
10$M$15
11$M$15
12$M$15
13$M$15
14$M$15
15end$M$15
end address
Cell Formulas
RangeFormula
O2:O15O2=CELL("address",INDEX(M2:M$15,MATCH("end",M2:M$15,0)))
 
Upvote 0
You could try something like below. However, I am wondering what you are going to do with this address next?
For most questions like this the next step can usually be completed (more efficiently) without the need to actually calculate this address.

24 01 17.xlsm
MNO
1
2$M$8
3$M$8
4$M$8
5$M$8
6$M$8
7$M$8
8end$M$8
9$M$15
10$M$15
11$M$15
12$M$15
13$M$15
14$M$15
15end$M$15
end address
Cell Formulas
RangeFormula
O2:O15O2=CELL("address",INDEX(M2:M$15,MATCH("end",M2:M$15,0)))
Hi Peter_SSs, thank you so much for your reply.

What I ultimately need to do is, in column N, count the total number of unique weeks (using the week numbers in column L) during each period, the start and end of each period being the rows were "start" and "end" are specified in column M.


So for example, for the period from M2 to M8, the total number of unique weeks would be 4 (i.e. weeks 41, 42, 43,45) and from M9 to M15 would be 5 and so on.

(I've uploaded a new image which I hope will help make what I'm trying to describe make sense.)

I would very much appreciate your help if there is a way I could achieve this next step.


Many thanks again,

AnyaK
 

Attachments

  • Screenshot 2024-01-17 153907.png
    Screenshot 2024-01-17 153907.png
    41.4 KB · Views: 7
Upvote 0
For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Also best to include the final actual results so we can see what you are aiming for and where it should be.

Is it something like this (which doesn't require the cell addresses) that you are looking for?

24 01 18.xlsm
LMN
1
241start4
341 
442 
542 
643 
743 
845end 
942start5
1043 
1144 
1244 
1344 
1449 
1550end 
Count unique
Cell Formulas
RangeFormula
N2:N15N2=IF(M2="Start",COUNT(UNIQUE(L2:XLOOKUP("end",M2:M$100,L2:L$100))),"")
 
Upvote 1
Solution
For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Also best to include the final actual results so we can see what you are aiming for and where it should be.

Is it something like this (which doesn't require the cell addresses) that you are looking for?

24 01 18.xlsm
LMN
1
241start4
341 
442 
542 
643 
743 
845end 
942start5
1043 
1144 
1244 
1344 
1449 
1550end 
Count unique
Cell Formulas
RangeFormula
N2:N15N2=IF(M2="Start",COUNT(UNIQUE(L2:XLOOKUP("end",M2:M$100,L2:L$100))),"")
Hi Peter,

Thanks so much for your reply.

This is exactly what I need.

Thanks again so much.

Regarding XL2BB, I regret that at work we are not allowed to have this installed.

Thanks again for your help, I will implement this now.

AnyaK
 
Upvote 0
Regarding XL2BB, I regret that at work we are not allowed to have this installed.
Understand. So if you ask any other questions, state that in your first post so that we don't keep asking. ;)
Two alternatives, not so good but generally better than a picture ..
  • Simply copy/paste from Excel and explain what the range is and what any formulas are, at least we can copy the values.
  • Upload a simple sample file to DropBox, OneDrive, Google Drive etc and provide a public shared link. Downside is that many helpers choose not to download or are prevented from doing so by their workplace restrictions. :eek:
This is exactly what I need.

Thanks again so much.
You are welcome. Glad to help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

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

Which adblocker are you using?

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
Back
Top