[vlookup is not enough]

[Karol_tom]

I have multiple values in my sheet and i want to get last entry value.
Unfortunately vlookup function get first value, so I try to write new
function, but it doesn't work.
Maybe somebody now why ?


======================================
===========
Public Function getdateFROMcell (mycell)
Windows("mySHEET.xls").Activate
''i want get row of last entry with value from mySHEET.xls
z = Range("B5").End(xlDown).Row

For i = z To 3 Step -1
If mycell= Cells(z, 2).Value Then
'column 8 contains date with I want get
getdateFROMcell = Cells(z, 8).Value
Exit For
End If
Next i

End Function

===========

 

[Jim Rech]

How about trying an array formula*?

=OFFSET(INDEX(A1:A10,MAX((A1:A10=C1)*ROW(A1:A10)-ROW(A1)+1)),0,7)

Here the lookup table is in A1:A10 and value to be looked up is in C1. This
returns the value in col H on the same row as the last match in A1:A10.

* Array formulas must be entered with Ctrl-Shift-Enter rather than by
pressing just Enter.

 

[Lynz]

How about trying an array formula*?

=OFFSET(INDEX(A1:A10,MAX((A1:A10=C1)*ROW(A1:A10)-ROW(A1)+1)),0,7)

Here the lookup table is in A1:A10 and value to be looked up is in C1.
This returns the value in col H on the same row as the last match in
A1:A10.

* Array formulas must be entered with Ctrl-Shift-Enter rather than by
pressing just Enter.

I think there may be a formula that uses SUMPRODUCT AND MAX, sorry not
much help but came across it while looking for something else. One of
the experts may know.
lyn

 

[JET Fusion]

Instead of using an array formula, use the MAX() on the entry row.

---JET_MMPro