IF,AND, function displaying last date

Hello,

I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. Can anyone assist? Perhaps a whole new formula suggestion?

What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date.

This is what I have so far:
=IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C))

I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in.

So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".

Thanks for the help in advance.

In order to get a precise answer more quickly, upload a sample workbook that . . .

1. Exactly duplicates the structure of your real workbook, and
2. Contains representative, but non-sensitive dummy data, and
3. Manually illustrates your desired outcome

If your dates in Column C are sorted such that the last date for any given combination is always the last record you can use a LOOKUP approach, eg:

Please Login or Register  to view this content.

If not sorted in this manner you could use a MAX based Array but given you're using XL2003 you can not use entire column references, so - using same ranges as above

Please Login or Register  to view this content.

that highlighted in red is imperative...

XL2007 and beyond you can use entire column references in Array formulae etc but you shouldn't... performance impact is noticeable
(1m+ rows to process per column referenced...)