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?  Register To Reply

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

Regards, TMS  Register To Reply

=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.  Register To Reply

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.  Register To Reply

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.  Register To Reply

(Duplicate post)  Register To Reply

(duplicate post)  Register To Reply

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.  Register To Reply Originally Posted by daviddc114 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!!  Register To Reply

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?  Register To Reply

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!!!  Register To Reply

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)  Register To Reply