Index, Large, Match functions

Hello,

I have a large table of data (with a small sample shown below) from which I am trying to extract a specific date in column A. Column A shows the date and time sorted in ascending fashion. I would like to find the date/time at which the latest "Buy" of any given item was made. Also, the date/time at which the latest "Sell" was made. I've been trying to use the Index, Large, and Match functions, but can't quite get it to work. This is the latest attempt:

=INDEX(A4:E1241,LARGE(IF(MATCH("Item 56"&"Sell",$C$4:$C$1241&$E$4:$E$1241,0),ROW(A4:E1241)-ROW(A4)+1,0),1),1)

I'm self taught in Excel with 20 years experience and have no VB knowledge. Any help would be appreciated.

A B C D E
Date/Time, Qty, Item, Price, Action
4 7/2/2012 4:45, 1, item 3, 64,969,996, Sell
5 7/2/2012 5:10, 1, item 56, -20,950,999, Buy
6 7/2/2012 6:21, 1, item 23, 8,499,990, Sell
7 7/2/2012 6:33, 1, item 5, 9,348,997, Sell
8 7/2/2012 6:34, 2, item 7, 58,989,996, Sell
9 7/2/2012 7:09, 1, item 56, 10,209,913, Sell
10 7/2/2012 7:57, 3, item 56, 67,999,995, Sell
11 7/2/2012 7:57, 1, item 6, -28,795,600, Buy
12 7/2/2012 8:05, 8, item 3, 43,193,399, Sell
13 7/2/2012 8:53, 1, item 5, -43,193,400, Buy

Apologies for the formatting.....

Hello Toddmike,

Welcome to the forum.
You can use the below array formula:-
{=MAX(IF(($C$1:$C$10=$G2)*($E$1:$E$10=H$1),$A$1:$A$10,""))}

See attached:-Index Large match functions.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

This should work for the latest "item 56" being sold:

Formula:

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter.

Augment it to fit all your purposes.

Thank you both for the quick responses. Your equations worked perfectly. I was obviously making it harder than it really is.

You are welcome Todd. .

Cheers


suggest u to mark this thread as SOLVED.

Regards,
DILIPandey

<click on below star if this helps>