Hi,
I'm sure this can be done with a formula but I'm stumped so heres a
function. Alt +F11 to open
VB editor. Right click 'ThisWorkbook' and insert
module and paste the code in
Call with
=Bestrow(A1:A100,"001")
or use a cell reference for the text value
=bestrow(a1:a100,D1)
Note it will work anywhere in the sheet but always uses the 2 columns to the
right of the range you specify in the function call.
Function bestrow(rng As Range, lval As String)
For Each c In rng
If c.Value = lval Then
founddate = c.Offset(, 1).Value
If founddate = bestdate Then
bestrow = c.Offset(, 2).Value
End If
End If
Next
End Function
Mike
"cardan" wrote:
On Feb 19, 10:04 am, Mike H wrote:
OOPS,
I missed your 'numbers' are text
=VLOOKUP(MAX(IF(A1:A12="001",B1:B12)),B1:C12,2,FAL SE)
Don't forget the array instructions
Mike
"Mike H" wrote:
Hi,
You can do it with a formula
=VLOOKUP(MAX(IF(A1:A12=1,B1:B12)),B1:C12,2,FALSE)
This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array
Mike
"cardan" wrote:
Hello,
I have a series of serial numbers (converted to text), dates and
amounts in column format. The number of serial numbers changes
depending on the date sold. The amount it sold for also changes. for
example
001 1-3-09 $300
001 1-5-07 $250
001 2-3-02 $550
002 4-3-01 $600
002 5-9-05 $300
003 6-9-07 $200
There are numerous serial numbers that repeat numerous times (about
5000 rows of code)
I would like to write a formula that finds the maximum date for the
001 for example and then returns the dollar figure in that row. Is
this a formula or do I need VBA?
Thank you for your time
Hi Mike, Thank you for your response. I am getting a number but it is
not the correct one. Upon evaluating the formula, it seems to be
pulling the correct date, but there are multiple dates that are the
same so I think it is pulling the amount from the first date that
equals the max date for "001", regardless of the "001" criteria. Any
way to correct?