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

=MAX(IF($B$1:$B$1000=$F2,$A$1:$A$1000))
confirmed with CTRL + SHIFT + ENTER

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