Matching Value in F:F to lastest date/time in A:A

Morning All,

I have to extract information from an imported .csv file. In the attached mock up A2:B223 is the data being imported. A macro list the individual values from B:B & C:C in F:F & G:G.

I'm interested in a formula for I:I that will match the values in F:F to the most recent timestamp in A:A.

Anticipated results are in I2:I5

Thanks... as always

You can use a MAX array

Please Login or Register  to view this content.

avoid using entire column references... if you're using VBA you can calculate the above in VBA.

Equally you could consider just creating your output table (F:I) with a PT... that would give you all your requisite info - eg:

Name & ID as Row Labels, ID again as Data Field set to COUNT, DateTime as Data Field set to MAX formatted as Date Time.

DO

I follow what the formula is trying to do; however, when placed in K2 the result is the MAX value from A2:A1000 which does not match the expected name in F2. When dragging the formula down only 1/0/1900 0:00 shows in the remaining cells K2:K4 which are formatted identically to K2.

I've also notice that you are a big proponent of PT's. I definately need to find the time to use them effectively. Unfortunately at work I need to muttle along with the established practices for now....

The suggested formula is an Array so must be confirmed with CTRL + SHIFT + ENTER (not just Enter) - once Array is set the formula will appear encased within { }

IMHO Pivot Tables should be the very first thing anyone learns when using XL.
PTs are so simple it's untrue and offer unprecedented reporting power at the click of a few buttons... with the introduction of PowerPivot with XL2010 I think they look set to take over the world!

I should have realized the Ctrl+Shift+Enter from

You can use a MAX array

. I must have had a brain fart! Thanks for the replies. I've been wanting to learn more about PT's but fell in love with Excel's formulas. I'll try to make time to do some reading/practicing.

Thanks