+ Reply to Thread
Results 1 to 2 of 2

vlookup formula need to set it up to find the date with the most recent update

Hybrid View

  1. 10-21-2012, 03:51 PM #1
    pmiguel
    pmiguel is offline
    Registered User
    Join Date
    10-21-2012
    Location
    parsippany
    MS-Off Ver
    Excel 2010
    Posts
    2

    vlookup formula need to set it up to find the date with the most recent update

    Hello!

    I have this workbook that I need help with. Workbook contains 2 tabs on the second tab I have listed all the error files and the measures taken to correct those errors. On the first tab I have a running list of all the invoices which at times will show the same invoice # more than once. On the column for the status I currently have a VLOOKUP formula to lookup on 2nd tab the matching invoice # and populate the original error message, date and the corrections that were done. The issue is that when that invoice get resubmitted the formula is picking up the error message again even though it got accepted.

    I need to tweak the formula so that it looks up the invoice # but return the error message only when there is an error. If the invoice number contains an error the second time I need it to pick up the second error message and not the first one. Does that make sense? Here's the formula: =IFERROR(VLOOKUP(B82,'Payor Rejected Detail'!$B$1:$D$3538,3,FALSE),"")

    I have attached the workbook so it can be viewed. Can someone please help?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-21-2012, 05:03 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,969

    Re: vlookup formula need to set it up to find the date with the most recent update

    ok im not sure how you determine when to use the 2nd instance of an invoice (when it appears a 2nd time, i guess??) and when it does appear multiple times, what value do you want returned?

    maybe to get you started, try this. i added a helper column on sheet2 after B, and used this formula, copierd down...

    =VALUE(B2&IF(COUNTIF($B$2:B2,B2)>1,2,1))

    this will take the invoice number from B and add a 1 (if it's unique) or 2 if there is more than 1 entry of that invoice

    If there will be more than 2 entries, and you want to be able to reference the 2nd or 3rd "response", then change the formula to...
    =VALUE(B2&COUNTIF($B$2:B2,B2)). this will add 1, 2, 3 etc to the end of the invoice, depending on how many times it the invoice occurs.

    then you could use the same process on sheet1 to put up the nth entry for each invoice?

    the helper columns can be hidden. let me know if this is something you could work with, and we can start putting something together
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1