+ Reply to Thread
Results 1 to 8 of 8

If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

  1. 05-31-2013, 01:22 PM #1
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    81

    If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    In a workbook I have two sheets.

    Sheet 1: Statement with 1000+ invoice numbers and amount etc (third party)
    Sheet 2: Download of invoices I have on my system

    I want to do a look up in 'sheet 1', to match off what I have or don't have from the third party statement.
    Therefore if invoice number on 'sheet 1' matches invoice number on 'sheet 2', do their corresponding figures match exactly? Yes or No. I assume if there is no invoice on my system (sheet2) the search would bring up a #N/A in sheet 1.

    Can I actually do this in one formula or would it be two different ones?
    Last edited by MushroomFace; 06-01-2013 at 10:26 AM.
    Reply With QuoteRegister To Reply
  2. 05-31-2013, 01:38 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    Let's say your invoice numbers and amounts on sheets 1 and 2 are in A2:B2000
    On Sheet1 in C2
    =B2=IFERROR(VLOOKUP(A2, Sheet2!$A$2:$B$2000, 2, FALSE),"") copied down
    That will give TRUE for matches and FALSE for not an exact match or missing invoice
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 05-31-2013, 01:38 PM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,837

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    Assume invoice number in column A and amount in column B. Try this in C1 of Sheet2:

    =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Absent",IF(B1=VLOOKUP(A1,Sheet1!A:B,2,0),"Correct","Wrong amount"))

    then copy down. This doesn't check for duplicates, which could occur in either list.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  4. 06-01-2013, 10:24 AM #4
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    81

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    Hi both

    Thanks for your help.

    ChemistB, it didn't quite work but maybe I could have re-jiggled it if I understood the formula, i'll read up on IFERROR's.

    PeteB, this forumla worked perfectly. Thanks again.
    Reply With QuoteRegister To Reply
  5. 06-01-2013, 10:34 AM #5
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    81

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    ChemistB I lie. It did work the second time!
    Reply With QuoteRegister To Reply
  6. 06-01-2013, 11:38 AM #6
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,837

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    Who is PeteB ?

    Pete
    Reply With QuoteRegister To Reply
  7. 06-01-2013, 11:50 AM #7
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    81

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    Pete*

    Typo!
    Reply With QuoteRegister To Reply
  8. 06-27-2013, 02:08 PM #8
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    81

    Re: If reference A is in sheets 1 and 2, does the amount match exactly in both sheets?

    A Quick one in relation the the original question.

    Can this be done or amended, if 'sheet1' has several lines with the same invoice reference, the sum of all these lines must =the figure in 'sheet2'.

    If not, i'll do a new post.

    Thanks in advance.
    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