Help with formula

[califoh]

I need some help with a formula. I believe I need to use an IF statement but I am not sure how to set it up.
I have a workbook with 2 different spreadsheets. Each spreadsheet contains customer information.
For example Customer name, Account number and so on. In one of the workbook there is an email address attached to the customer. In the other workbook there is not an email field. I need to compare the two spreadsheets and find out which customers have an email address. Both worksheets contain the same customer list but with different information in each. I need to know in the worksheet that does not list email addresses which of those customers have an email address listed in the other worksheet.
Thank you,

 

[debooo]

could you post a small example of the info (doesnt have to be the acutal email addresses) just what the different sheets say. i think i could def help you if i saw a small example of exactly what you meant

 

[califoh]

Thank you, here is an example. Much smaller than the real file. The actual file contains hundreds of names. On sheet one, in Column C I want it to say yes or no as to wether the same customer has an email attached to their name on sheet 2. I would also like the email address entered in column D if possible. Sorry when I copied and pasted it did not line up properly. Did not see where I could attach a file.
This is sheet1
A-Customer
B-Account Number
C-Email yes or no
D-Email address
Customer Name Account Number Email: Yes or No Email Address
ABC Company 129696
Heating and Cooling 232352
Davis Tractor Supply 985454
Lawn's Are Us 745215
Lowery Heating 745212
Doug's Plumbing 524784
This is sheet2
A-Customer Name
B-Account NumbeC-Email
D-Phone
Customer Name Account Number Email Phone
ABC Company 129696 ABC@embark.com 963-958-9696
Heating and Cooling 232352 745-521-9696
Davis Tractor Supply 985454 852-985-9652
Lawn's Are Us 745215 Lawns@yahoo.com 852-854-9696
Lowery Heating 745212 852-745-7474
Doug's Plumbing 524784 dough@gmail.com 852-369-2222

 

[ExcelChampion]

You need to use a lookup function such as LOOKUP, or VLOOKUP.

 

[debooo]

for the yes/no i used this formula

=IF(ISTEXT(VLOOKUP(A3,Sheet3!$A$37:$C$39,3,0)),"Yes","No")

and to show the email i used this

=VLOOKUP(A3,Sheet3!$A$37:$C$39,3,0)


i used sheets 2 and 3 so you'd have to change the formula if your sheets have different names

 

[califoh]

Thank you so much for the quick response. Sorry to sound like a dummy but can you explain what each function of the vlookup and If statement do. I am having a difficult time applying the formula to my actual spreadsheet. I understand to rename the worksheets but I am getting an error. If you can explain what each function does it would help.
=IF(ISTEXT(VLOOKUP(A3,Sheet3!$A$37:$C$39,3,0)),"Yes","No")
I understand this to say if text exists in sheet 3 in column a. but I am a little confused by the rest of the formula.
Thanks again.

 

[debooo]

this vlookup takes what is in cell A3, in this case it would be the customers name and it searches for that same text in the first colum in any range you specify, in this case the range is the 2nd spreadsheet you have, the range includes 3 colums the first being the customer names.

if the vlookup finds the same text it will return the information that is in the colum you specify of the the row in which it located the match.

so the '3' in the formula is tellling excel to find the customers name and return the information in col 3 of the same row.

the last part of the formula is basically telling excel that the match must either be exact or to simply find the closest match (i almost always use 0 which is exact)

so in the 2nd sheet you have the 3rd col of info which is an email adress (if it exists) the vlookup finds the customer name in sheet 1 goes to sheet 2 finds that customers name and returns the 3rd col of info

this vlookup will now either return an email address if it is there or nothing if that particular customer hasnt an email. so the istext function is checking what the vlookup finds and saying either true it is text or false it is blank.

the if statement has 3 parts (if something is true, do this, if not do this)

i would suggest trying it in steps, use the function tool in excel to make the vlookup, it takes you through it farily straighforawd.

i dunno if any of this makes sense, its really not complicated but its hard for me to explain.

goodluck

 

[califoh]

Thank you.