Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VLOOKUP help (2003)

    I have a situation where I have a souce sheet that has different iterations of the same event number with different as of dates and different account numbers. I need a formula to return whether the event has completed. The results sheet will be updated daily with the completes and fails. The same event could fail every day for several days before completing. I need a vlookup (or something) that will check the event number and the as of date to return a status. Every time I try to use a vlookup or index/match, I keep getting stuck. Am I missing something basic here?
    thanks
    christine

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP help (2003)

    I don't understand what the "As Of Date" has to do with the question you are asking. It looks like you could just check the status column for the event number you are interested. I can't see any way to tell the status from the "As Of Date" column. Also, when is an event considered complete? When any status row shows "completed"?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP help (2003)

    The same event needs to be completed several times with different "as of" dates. So event 250634 for an as of date of 4/30, shows on the results with a "not processed" status. But the same event 250634 for an as of date of 5/31, shows on the results sheet with a "completed" status. I want the formula built into the yellow highlighted section of the source sheet. I put what the results should be just to identify them. So my logic (if it can be called that) is:

    If the event is on the results sheet, if it matches the as of date, if it is complete, return a complete status.
    thanks
    christine

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP help (2003)

    OK, that makes more sense. However, I am still having problems.

    1- On sheet Source cell F6, you show "Not Processed", but the results sheet shows Completed for the same "Date as of".

    2- Cell F7 shows "future dated", but the results show nothing for this date.

    3- Will there be more than one event on each of the source and results sheets? If so, how will they be arranged?
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP help (2003)

    You're absolutely right on point #1. That was an error on my part. On point #2, just to make this more fun, if the "Date Next" is greater than the reporting date (today), the status will show "future dated." That's the easy part of the formula so I forgot about it. It's the meat in the middle that I can't get. See new additional examples in the attachment.
    thanks
    christine

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP help (2003)

    So does the account not matter, just the event and the date?

    Does this do anything close to what you want? It assumes if it doesn't find the event and the As Of date that it's future dated. Also, it adds a column on each sheet to concatenate the event and the date for the vlookup.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP help (2003)

    YES YES YES!!!!

    I think this is it. I couldn't remember for the life of me what to do about the 2 different criteria. I totally forgot about concatenate. Thank you guys so much.
    thanks
    christine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •