Ask Experts Questions for FREE Help !
Ask
    StuMegu's Avatar
    StuMegu Posts: 576, Reputation: 64
    Senior Member
     
    #1

    Aug 25, 2010, 03:10 AM
    Find Most recent table entry that is not current month
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    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's Avatar
    StuMegu Posts: 576, Reputation: 64
    Senior Member
     
    #3

    Aug 25, 2010, 09:42 AM


    Thanks, I've (hopefully uploaded a sample workbook with this in now.
    Attached Files
  1. File Type: xls book1.xls (22.5 KB, 112 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    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.
    Attached Files
  3. File Type: xls Book1-2.xls (25.5 KB, 129 views)
  4. StuMegu's Avatar
    StuMegu Posts: 576, Reputation: 64
    Senior Member
     
    #5

    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! :)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

I want to print recent employment w-2 record for 2009 but can't find them [ 1 Answers ]

I want to print two w-3 employee records for 2009 from recent employment

Get max of date field in a table for a particular month [ 3 Answers ]

Hi, I have data in a table as follows: Key date 101 4/25/2006 101 4/26/2006 101 4/27/2006 101 4/28/2006 101 5/25/2006

How to record revenue where part is received in the current month & the remainder the [ 1 Answers ]

I'm trying to check my grandaughter's homework and I need to know What is the entry for recording earned revenue of $8,000 of which $3,000 is collected in cash and the balance is due the following month.

Updfating a post closing trial balance into a current month. [ 1 Answers ]

:confused: I was given a completed post closing trial balance for February. I was asked to set up the general journal, auxiliary petty cash, payroll register and the special journals. The book indicates to "be sure to update ledger accounts based on givan information in post-closing trial balance...

When paying the building lease in adavance of the current month. [ 2 Answers ]

I'd like to pre-pay the building lease and don't want the expense to be doubled on my income statement for the current month since I've already paid the rent for this month. I believe that I need to set up a pre-paid GL account on the balance sheet. I don't have any pre-paid accounts on my chart...


View more questions Search