+ Reply to Thread
Results 1 to 3 of 3

Conditional Lookup value with subtraction

Hybrid View

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    38

    Conditional Lookup value with subtraction

    I have Two Files
    and i need a Simple formula to lookup value and subtract at the same time

    eg in Sheet 1

    Name/ Pen/ Pensil/ Rubber
    Mark/100/250/650

    eg in Sheet 2

    Name/ Pen/ Pensil/ Rubber
    Mark/50/20/60


    So I need to Search Mark how many Pen he has in Sheet 2 and how many he is left with after deducting Sheet1 minus Sheet 2

    In Sheet 1 i need a Formula which will give me difference of 50 pens , 230 pensil, 590 rubbers

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Conditional Lookup value with subtraction

    In E2 enter :

     =SUM(Sheet1!B2)-VLOOKUP(Sheet1!A2,Sheet2!A:D,2,0)
    In F2 enter:

     =SUM(Sheet1!C2)-VLOOKUP(Sheet1!A2,Sheet2!A:D,3,0)
    In G2 enter:

     =SUM(Sheet1!D2)-VLOOKUP(Sheet1!A2,Sheet2!A:D,4,0)

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Conditional Lookup value with subtraction

    Use the VLOOKUPS as part of the calculation eg

    =VLOOKUP(A1,Sheet1Range,2)-VLOOKUP(A1,Sheet2Range,2)
    See attached.
    Attached Files Attached Files
    If someone has helped you then please add to their Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional subtraction (In between / List)
    By Zenarte in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2015, 05:27 PM
  2. [SOLVED] Help with conditional time subtraction
    By Khaldon in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-09-2014, 04:13 PM
  3. LOOKUP amd SUBTRACTION! HOW?!
    By damagas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2013, 09:30 AM
  4. Conditional formatting and subtraction
    By Bizzielizzie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-21-2013, 07:24 PM
  5. Lookup with subtraction as well as multiple entries.
    By genichigo in forum Excel General
    Replies: 2
    Last Post: 05-03-2012, 03:07 PM
  6. Conditional and cumulative subtraction
    By smokebreak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2010, 06:38 PM
  7. Conditional formula (subtraction)
    By dklein2149 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2008, 10:59 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1