Hi,

I want to perform a lookup on a table in excel (2003) to bring audit results back for the most recent previous month. I have already tried =LARGE(IF(A$5:A$5000=W5,C$5:C$5000),1) and that brings back the most recent entry in the table, but I don't want this... I want to bring back the most recent entry in the table that is not for this month and the table includes this month's entries.

the table looks something like this:

Supplier Year Month Result
SUP1 2010 1 0
SUP1 2010 4 5
SUP1 2010 8 10
SUP2 2010 3 0
SUP2 2010 4 5
SUP2 2010 5 10

So I want to bring back the most recent result that is not month 8 (current month) from this table.
for example the result from SUP1 would be 5 from April and the result from SUP2 would be 10 from May.

I hope I have explained the problem clearly, does anybody have any ideas?

Can you upload a sample workbook showing this table properly and where exactly you're trying to bring values over to?

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

Thanks, I've (hopefully uploaded a sample workbook with this in now.

I added a key column formula to make this simple in column G. You can hide column G if you wish.

I also activated Excel's "LIST" feature on your data table so that as you add data to the bottom the formula in column G will extend itself down automatically.

There are two different formulas in M2 and N2... copied down.

Wow, thanks JB, I'll have to learn how to use these functions to make use of them in the future, top marks! :)