Urgent!!-Find Cell Address of Last occurrence of a String in a column

[lesliewheeler]

Hi,

Need a little help with a formula, I am trying to find the cell address of the last occurrence of at string "LLL" in a column.

Range- (I4-I3001)

the formula in cell A1 will check the last occurrence of the txt "LLL" in the above column (I4:I3001) and return the address of the same.

EG:-
LLL occurs in I15, I90, I56,

Result box in A1 of the formula will show I56. This will dynamically get updated as more occurrence happen in the range


I HOPE SOMEONE CAN HELP ME QUICKLY.....

Appreciate It!!

 

[jim may]

Why wouldn"t Result box in A1 of the formula show I90 before we start, since 90 is greater than 56?

 

[Fluff]

How about
="I"&MAX(IF(I4:I3001="LLL",ROW(I4:I3001)-MIN(ROW(I4:I3001))+4))
This is an array formula & needs to be confirmed with Ctrl Shift Enter, not just Enter.

 

[lesliewheeler]

Thanks a ton brother it worked!!!!...Really appreciate your help

 

[lesliewheeler]

Correct...My Bad.... But he gentleman below had understood and gave a kick *** solution

 

[steve the fish]

Heres its row:

=LOOKUP(2,1/($I$4:$I$3001="LLL"),ROW($I$4:$I$3001))

You can concatenate "I" onto the front for its address. Why the address by the way?

 

[lesliewheeler]

I need the cell number for using it as a refrence in another formula... but guess it didnt do much good as I am getting a circular reference error... i tried using cell index and match it is shows the first instance not last... can u help with this result using the =cell(“address” formula

 

[steve the fish]

It is very unlikely you need the cell address to pass to another formula. What is it you try to do in the other formula?

 

[lesliewheeler]

I am trying to link the starting point of range to the address of the highest occurrence cell.. which changes dynamically as data updates

 

[steve the fish]

So what is it your formula is intending to do? Paste it here so we can maybe tell.