+ Reply to Thread
Results 1 to 9 of 9

how to use vlookup return only some condition satisfies

  1. 04-28-2014, 08:15 PM #1
    gillani77 is offline
    Registered User
    Join Date
    02-20-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    how to use vlookup return only some condition satisfies

    hi
    i m having same problem but i need to get data from other sheet. Can i use this formula. here is details of my problem.
    HI Sir,
    I have 2 sheets in same spread sheet with following columns in each sheet.
    1. zone
    2. date
    3. representative
    4. commission earned
    I want to copy commission earned on sheet 1 column “Y”( namely RICARDO)from sheet to 2 (namely OCT – FEB) ,if following condition is fulfilled/ satisfied.
    All these values (Zone , date, representative and commission) of both sheets is same then commission of 2nd sheet should be copied on sheet 1 column “K”

    I am using excel 2010 , window8 and an HP lap top.

    thanks

    Kind regards.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-28-2014, 08:26 PM #2
    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
    53,044

    Re: how to use vlookup return only some condition satisfies

    If you have both sheets in the same workbook, this will get you started. I put this in Oct-Feb E2, copied down...
    =SUMIFS(Sheet1!D:D,Sheet1!A:A,'Oct-Feb'!A274,Sheet1!B:B,'Oct-Feb'!D274,Sheet1!C:C,INT('Oct-Feb'!B274))
    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
  3. 04-28-2014, 09:12 PM #3
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: how to use vlookup return only some condition satisfies

    May be this.....
    Why did you upload two sheets separately? I assume that both these sheets are in the same workbook.
    Assuming that your workbook contains two sheets "RAICARDO" and "Oct-Feb"
    On Sheet "RAICARDO"

    Please Login or Register  to view this content.
    and then copy down.

    Is this what you want?

    Moreover if your current excel version is 2003, the formula suggested by Ford will not work for you.
    Last edited by sktneer; 04-28-2014 at 09:15 PM.
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  4. 04-28-2014, 09:15 PM #4
    gb1331 is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Warsaw, Poland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: how to use vlookup return only some condition satisfies

    Hi,
    I found this formula working:
    =IFERROR(INDEX('Oct-Feb'!D:D,MATCH(1,(A2='Oct-Feb'!A:A)*(B2='Oct-Feb'!D:D)*(C2<='Oct-Feb'!B:B)*(C2+1>'Oct-Feb'!B:B)*(D2='Oct-Feb'!C:C),0)),"")

    you should put it in K2 in RAICARDO sheet and confirm it with CTRL+SHIFT+ENTER instead of ENTER. You can copied down but this formula evaluates slowly because your sheets are huge but don't know if its possible to write significantly faster formula even if entries are ordered by date.

    Does it help you?

    Best regards,
    gb1331
    Reply With QuoteRegister To Reply
  5. 04-29-2014, 12:41 AM #5
    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
    53,044

    Re: how to use vlookup return only some condition satisfies

    I meant to add this to my previous post...

    I notice that the file you uploaded indicates 2007 or later (.xlsx), but your profile says 2003. Please update your profile as necessary, members tailor questions based on your excel version
    Reply With QuoteRegister To Reply
  6. 04-29-2014, 06:34 AM #6
    gb1331 is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Warsaw, Poland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: how to use vlookup return only some condition satisfies

    Hi,

    Quote Originally Posted by gb1331 View Post
    don't know if its possible to write significantly faster formula even if entries are ordered by date.
    I achieved to write faster formula. I modified sktneer formula because mine and FDibbin's probably don't work in Excel 2003.
    I assume that data is ordered by date, so my formula finds smaller range with proper date and in this range do what sktneer's formula does.
    I use 2 additional columns to simplify my formula:
    In RAICARDO sheet:
    In G2 cell:
    Please Login or Register  to view this content.
    In H2 cell:
    Please Login or Register  to view this content.
    In I2 cell:
    Please Login or Register  to view this content.
    Here is the link to whole worksheet:
    http://speedy.sh/hkdEP/Sheet1.xlsx

    I hope this will be useful.

    Best regards,
    gb1331
    Reply With QuoteRegister To Reply
  7. 04-29-2014, 07:22 AM #7
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: how to use vlookup return only some condition satisfies

    IN K2 of Sheet2 file use , then drag down.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 04-29-2014, 05:32 PM #8
    gillani77 is offline
    Registered User
    Join Date
    02-20-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: how to use vlookup return only some condition satisfies

    Hi Guys,
    none of formula is working.
    I am using Excel 2013.
    Pls help me out.
    Reply With QuoteRegister To Reply
  9. 04-29-2014, 06:11 PM #9
    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
    53,044

    Re: how to use vlookup return only some condition satisfies

    Please upload your sample file here, not all members are able - or willing - to access file hosting sites
    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)

Similar Threads

  1. how to use vlookup return only some condition satisfies
    By gvgbabu in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-28-2014, 07:37 PM
  2. VBA to transfer data that satisfies a condition from one sheet to another in same workbook
    By KLL2014 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-08-2013, 12:23 AM
  3. A condition true if one element in a row of a matrix satisfies it
    By giovannioh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-29-2010, 07:09 AM
  4. Copying rows which satisfies a filtering condition
    By blurtoad in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-06-2007, 11:31 AM
  5. How to input cellcontent within worksheets if satisfies condition
    By Vinitha in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-03-2006, 08:00 AM

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