Formula to find first and last occurnce

[rizmomin]

Hello:

Please refer to attached file.

I need formula in I2 to give 1st occurence of text I1 in column G.
I need formula in I3 to give last occurence of text I1 in column G.

Let me know if you have any questions.
Thanks.
R

[Phuocam]

1st Occurrence =MATCH(I1,G:G,0)

Last Occurrence =AGGREGATE(14,6,ROW(G:G)/(G:G=I1),1)

[rizmomin]

Hello Phuocam:

Thanks a lot

Works for me

Riz

[kevinu]

Phuocam, why do you use the function 14? I see its "large" what does large do? I'm trying to understand the formula. I see that aggregate ignores errors which is nice so I want to be able to use it myself later.

[FlameRetired]

Another way. In I2 and fill down.

Please Login or Register  to view this content.

[kevinu]

Found this way with sumproduct and found it interesting.

Please Login or Register  to view this content.