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

[AnyaK]

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

 

[Peter_SSs]

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
	M	N	O
1
2			$M$8
3			$M$8
4			$M$8
5			$M$8
6			$M$8
7			$M$8
8	end		$M$8
9			$M$15
10			$M$15
11			$M$15
12			$M$15
13			$M$15
14			$M$15
15	end		$M$15
end address

Cell Formulas
Range	Formula
O2:O15	O2	=CELL("address",INDEX(M2:M$15,MATCH("end",M2:M$15,0)))

 

[Alex Blakenburg]

Just converting @Peter_SSs's solution to XLookup.

=CELL("address",XLOOKUP("end",M2:M$15,M2:M$15))

 

[Peter_SSs]

You could also do a lot less calculating with something like this.

24 01 17.xlsm
	M	N	O
1
2			$M$8
3			$M$8
4			$M$8
5			$M$8
6			$M$8
7			$M$8
8	end		$M$8
9			$M$15
10			$M$15
11			$M$15
12			$M$15
13			$M$15
14			$M$15
15	end		$M$15
end address

Cell Formulas
Range	Formula
O2:O15	O2	=IF(M2="end",CELL("address",M2),O3)

 

[AnyaK]

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
	M	N	O
1
2			$M$8
3			$M$8
4			$M$8
5			$M$8
6			$M$8
7			$M$8
8	end		$M$8
9			$M$15
10			$M$15
11			$M$15
12			$M$15
13			$M$15
14			$M$15
15	end		$M$15
end address

Cell Formulas
Range	Formula
O2:O15	O2	=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

 

[AnyaK]

Just converting @Peter_SSs's solution to XLookup.

=CELL("address",XLOOKUP("end",M2:M$15,M2:M$15))

Hi Alex,

Many thanks for your reply which, also worked successfully for me.

I appreciate your help,

AnyaK

 

[Peter_SSs]

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
	L	M	N
1
2	41	start	4
3	41
4	42
5	42
6	43
7	43
8	45	end
9	42	start	5
10	43
11	44
12	44
13	44
14	49
15	50	end
Count unique

Cell Formulas
Range	Formula
N2:N15	N2	=IF(M2="Start",COUNT(UNIQUE(L2:XLOOKUP("end",M2:M$100,L2:L$100))),"")

 

[AnyaK]

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
	L	M	N
1
2	41	start	4
3	41
4	42
5	42
6	43
7	43
8	45	end
9	42	start	5
10	43
11	44
12	44
13	44
14	49
15	50	end
Count unique

Cell Formulas
Range	Formula
N2:N15	N2	=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

 

[Peter_SSs]

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.

This is exactly what I need.

Thanks again so much.

You are welcome. Glad to help. Thanks for the follow-up.