[vlookup is not enough]

  • Thread starter Thread starter Karol_tom
  • Start date Start date
K

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

===========
 
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.
 
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.
Click to expand...
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
 
Back
Top