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?

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

Regards, TMS

=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.

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.

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.

(Duplicate post)

(duplicate post)

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.

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!!

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?

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!!!

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)

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

#### 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