Help with formula

califoh

New Member
Joined
Aug 5, 2005
Messages
6
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,
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

debooo

Board Regular
Joined
Nov 2, 2006
Messages
86
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

New Member
Joined
Aug 5, 2005
Messages
6
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
 

debooo

Board Regular
Joined
Nov 2, 2006
Messages
86
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

New Member
Joined
Aug 5, 2005
Messages
6
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

Board Regular
Joined
Nov 2, 2006
Messages
86
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
 

Forum statistics

Threads
1,181,730
Messages
5,931,707
Members
436,799
Latest member
BasOo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top