Excel Vlookup Query2

Zack
Zack used Ask the Experts™
on
Heyas,

I want to be able to select the name Truck (Truck_GE2) that has the most recent date assigned to it. How would I do this in Vlookup statement.  See the attachment which has a table of values.

Thank you.
Book1.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ZackGeneral IT Goto Guy

Author

Commented:
Nah it wouldn't work see attached again made an alteration. Need to also filter by the first column, the value I want is Truck_GE2.  So in summary it needs to work out values in column A have Truck contained in it and then to find the which one of those values has the most recent date.

Thank you.
Book1.xls
Top Expert 2010

Commented:
Try this array formula:

{=MAX(IF(LEFT(A1:A4,5)="Truck",B1:B4,""))}

To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  Excel will then display those braces to indicate that it's an array formula.
If you want to show the most recent date of each vehicle listed in column A, use the formula found in column C

Flyster
Book1.xls
ZackGeneral IT Goto Guy

Author

Commented:
Thats getting close but I want to list the Vehicle that has the "Most Recent" date assigned to it.

See attachment for the answer should be.

Thank you.



Book2.xls
ZackGeneral IT Goto Guy

Author

Commented:
My apologies for the delay my net connection has been down.
This will give you the results you want as long as column A is always in the same format, that being the vehicle type is always started with the third character in the string.
Book2.xls
ZackGeneral IT Goto Guy

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial