# Solved: excel find last cell in a list that equals a certain value



## blindbaker (Oct 10, 2005)

Hi
Im a bit stuck here. i want a formula (i figure its some sort of variation on the MATCH and ADDRESS formulas) that searches a list for a certain value and returns the cell reference of the last one in the list that it finds. Its simple to get the first in the list but i dont have a clue how to find the last in the list.
my formula for finding the first in the list is as follows

=ADDRESS(MATCH(J3,$E$9:$E$15000,0)+8,8)

any help would be appreciated

regards
BlindBaker


----------



## bomb #21 (Jul 1, 2005)

A helper column is one way.

Say you have values from A1 down. In B1,

=COUNTIF($A$1:A1,5)

copied down (5 is the value to match) increments at each "find". Therefore,

=MATCH(MAX(B:B),B:B,0)

finds the highest increment change. Does that make sense?

HTH,
bomb


----------



## blindbaker (Oct 10, 2005)

sorry im having a dumb day

bit lost

can you integrate it in to my current formula as shown in the previous post?

here is a break down of what i need it to do

i need to find the match part first so it finds the value of cell "J3" (which is variable) and gives its position in the list column E. then the address part of the formula gives me the cell reference for the row number supplied by the MATCH formula and column "H" which is the 8th column.

i cant seem to integrate your solution into 1 formula that would do this but find the last value in the list that equals cell "J3" i see what you mean by counting the number of values in the list that equal "J3" but not sure how to get it to give me the reference of the last entry in the list

im probably being extremely stupid and you explained it well but my head feels like jelly at the moment so you will have to excuse me.

thanks again


----------



## blindbaker (Oct 10, 2005)

Ah Ha!
i read it properly and now i understand (seems like it should work!). im just putting it into practice and will let you know if it works
thanks a lot


----------



## bomb #21 (Jul 1, 2005)

OK. I expect it can be done in one 'hit' with some fancy array formula, but you'd need someone smarter than I for that.

If it works out, you can use 'Thread Tools' to 'Mark Solved' (if that kind of thing appeals to you  )

Rgds,
bomb


----------



## blindbaker (Oct 10, 2005)

you are officially a genius!! it worked a treat. one thing though. where my range is 10000 rows it takes an absolute age for it to calculate the cells when the variable (J3) is changed!
i can live with that though.

thanks a million
BlindBaker


----------



## bomb #21 (Jul 1, 2005)

Taking an age doesn't constitute working a treat in my book. I _really_ recommend that you use code for this instead.

I put values 1 to 10 in A1:A10 & duplicated to row 10000. Then I put the match value in B1. With that set-up, the code below returned the last match of B1 (as an address) to C1 in about a second.

Post back if you need help installing code.

Rgds,
bomb

Sub Last_Match_Address()
If WorksheetFunction.CountIf(Range("A:A"), Range("B1")) = 0 Then GoTo 100

For Each Cell In Range("A1", Range("A65536").End(xlUp))
If Cell.Value = Range("B1").Value Then
Found = Cell.Address
End If
Next Cell
Range("C1").Value = Found

Exit Sub
100:
MsgBox "Search value was not found."

End Sub


----------

