Log in

View Full Version : Find Most recent table entry that is not current month


StuMegu
Aug 25, 2010, 03:10 AM
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?

JBeaucaire
Aug 25, 2010, 08:30 AM
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.

StuMegu
Aug 25, 2010, 09:42 AM
Thanks, I've (hopefully uploaded a sample workbook with this in now.

JBeaucaire
Aug 26, 2010, 09:03 AM
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.

StuMegu
Aug 27, 2010, 01:50 PM
Wow, thanks JB, I'll have to learn how to use these functions to make use of them in the future, top marks! :)