+ Reply to Thread
Results 1 to 12 of 12

Can someone please help me combine 2 formulas??

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Can someone please help me combine 2 formulas??

    hello, i am trying to combine the following 2 formulas into one complete formula. is it possible? any help would be greatly appreciated. thank you.

    =VLOOKUP(B1,DATA!A:C,2,FALSE)


    =(IF(C1<>"",SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","::"&C1),SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","")))



    also is there an easy way to combine formulas?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Can someone please help me combine 2 formulas??

    It might help if you tell us which cells those formulae are in and hence how you would like to combine them.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Can someone please help me combine 2 formulas??

    =VLOOKUP(IF(C1<>"",SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","::"&C1),SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","")), DATA!A:C,2,FALSE)
    Does that work for you?
    There is no "easy" way to combine formulas. You need to be able to fit the pieces together like a jigsaw puzzle. Figure out what each piece is doing and where it belongs.
    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

  4. #4
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    my apoligies,

    in cell E1 - =VLOOKUP(B1,DATA!A:C,2,FALSE)


    in cell F1 - =(IF(C1<>"",SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","::"&C1),SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","")))

    i would like E1 to complete first, and then F1.. i am not sure how to correctly combine them. E1 basically pulls some data from another worksheet in the workbook, and F1 pulls data from the same worksheet.

  5. #5
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    thanks for the try, however that did not work because once combined, it is trying to look at its own cell for data. perhaps if i upload the worksheet. right now, cell E does one part and then F the other, i would like this to be in one cell/formula.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    (Duplicate post)
    Last edited by daviddc114; 03-12-2013 at 09:35 AM.

  7. #7
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    (duplicate post)
    Last edited by daviddc114; 03-12-2013 at 09:36 AM.

  8. #8
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    ahh lol.. not my day, apparently i can not upload the worksheet for some reason

    basically

    in cell E1 - =VLOOKUP(B1,DATA!A:C,2,FALSE)


    in cell F1 - =(IF(C1<>"",SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","::"&C1),SUBSTITUTE(SUBSTITUTE(E1,"!",A1&"|"),"@","")))

    E1 uses Vlookup to search data on another sheet of the workbook, and F1 uses that data in its formula, is there a wat to modify ther F1 formula, so instead of looking to E1, it will look into the Vlookup that is in E1 so i can totally get rid of the E1 column. I hope i am explaining it clearly.. thank you for all the help.

  9. #9
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    Quote Originally Posted by daviddc114 View Post
    thanks for the try, however that did not work because once combined, it is trying to look at its own cell for data. perhaps if i upload the worksheet. right now, cell E does one part and then F the other, i would like this to be in one cell/formula.

    there is the worksheet, i guess it just took some time to upload. As you can see from the file, i want to combine column E and F into one, hopefully it is possible!!

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Can someone please help me combine 2 formulas??

    Need to replace E1 with your VLOOKUP every time it appears in the formula unfortunately.
    You can use Search and Replace

    =(IF(C1<>"",SUBSTITUTE(SUBSTITUTE(VLOOKUP(B1,DATA!A:C,2,FALSE),"!",A1&"|"),"@","::"&C1),SUBSTITUTE(SUBSTITUTE(VLOOKUP(B1,DATA!A:C,2,FALSE),"!",A1&"|"),"@","")))
    Does that work for you?

  11. #11
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Can someone please help me combine 2 formulas??

    AWESOME!! how did you figure that out so quickly!! it seems to work perfectly, what were you saying about having to use find and replace? Oh you mean to change all the formulas? no, i am actually just building the worksheet so i do not have to replace much.

    I see where the mistake was, with the true and false. I am new to excel, i have only been working with it for a few months and i am getting better at it however i still have much to learn on formula building. I especially need to learn more about the TRUE and FALSE. Where do you all suggest I can learn more? thanks again!!!
    Last edited by daviddc114; 03-12-2013 at 09:56 AM.

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Can someone please help me combine 2 formulas??

    You can use Find and Replace (CNTRL H). Make sure that under "Options" it says "Look in Formulas"
    Find E1
    Replace with VLOOKUP(B1,DATA!A:C,2,FALSE)

    Glad I could help.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1