How to Return the row# of the 2nd Occurrence using MATCH( ) Function?

[htawfik]

Dears,

I know how to use MATCH() Function to return the row number of which the look-up value occurs, what I need to do is to return the row number of the second occurrence of this look-up value, is there any way to do that?

[benishiryo]

welcome to the forum. based on your title, the answer is no. you cannot use match. you can use an array formula with SMALL though. say your data is:
Data Range

	A
1
2	a
3	b
4	c
5	a
6	c

MATCH would be:
=MATCH("a",A2:A6,0)

finding 2nd occurrence would be:
=SMALL(IF(A2:A6="a",ROW(A2:A6)-ROW(A2)+1),2)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

so if A2:A6 is "a", return the row number of A2:A6. it will give you
{2;FALSE;FALSE;5;FALSE}
what you need is 1 & 4. so minus the starting row using -ROW(A2)(i.e. 2) and add back 1

[sktneer]

If you have any problem in implementing the formula suggested by benishiryo, Please find the attached sheet to see how this works.

[htawfik]

It worked just fine. Thank you both

[sktneer]

Glad to help you. Thanks for the feedback.
If you have got the answer, please mark your thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

[htawfik]

I have a problem, as when I started using the formula mentioned above by benishiryo, the file is slowly calculating now, 2-3 minutes to calculate the arrays.
Given that the array is repeated 400 times in 1 column, any help?