Pick the latest value using vlookup

[imran91]

Hi guys.

I want to know how should i use the vlookup (or any other suitable formula) to pick the latest value. I have two workbook. First workbook will be the master and second is the control. Let say in master wb row 2, I key in ABCD and in control wb also ABCD but in row 20 and row 24 or row 27. Later, I put the date and time in two separate columns in the control wb for ABCD in both row 20 and row 24. What i want is that the formula will pick the greatest value of the date and time between this two rows and placed it in one column in master wb. So, anyone knows how to do the vlookup for this situation?

Thank you.

[mrice]

If you sort the control wb by date/time descending, VLOOKUP should pick to the most recent value.

[imran91]

hi mrice. thanks for the idea but that is not what i'm looking for. the system must be automatically.

[FDibbins]

find the latest date using max(), then use that inside an index/match or use vlookup if the date is on the left

[TheNewUnion]

=MAX(IF($A$2:$A$21=J3,$B$2:$B$21)) and enter as an array formula

in this example, my data table is A2:B21 Colum A contains the value I am looking up, column B contains a the value I want to evaluate and J3 is the value I am looking up.

Can't seem to upload at work, so will try later.

[imran91]

Hi TheNewUnion. Thank you for the formula. It really work. But it only work for date, not time. I want it to work for both date and time. I put cell B2 13/12/2012, cell C2 9:30 AM and the next row, cell B3 12/12/2012, cell C3 4:00 PM, your formula will return the value 13/12/2012 and 4:00 PM but I want it to return the value 13/12/2012 and 9:30 AM because 4PM is greater than 9:30 AM.

[imran91]

Hi sorry. I got it. I just combine both cells (date and time) into one cell and it function well. Thank you so much