Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to look up data from two different sheets. I need to reference data in one column on one sheet with data in one column on another sheet. Then if they match have a Yes filled in on a separate column in the second sheet.
Solved by M. L. in 53 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 06/08/2018 - 06:25
HI
Excelchat Expert 06/08/2018 - 06:25
It's pleasure to help you today.
User 06/08/2018 - 06:25
hello
Excelchat Expert 06/08/2018 - 06:25
You wan to check, whether the data exists in another sheet or not?
User 06/08/2018 - 06:25
I have two sheets and i need to see if the data matches
Excelchat Expert 06/08/2018 - 06:26
Ok. I am giving an example.
User 06/08/2018 - 06:26
then if they match i need a new column to say yes
Excelchat Expert 06/08/2018 - 06:27
I am working on the example. Please review.
User 06/08/2018 - 06:27
oh sorry i meant from two different workbooks
Excelchat Expert 06/08/2018 - 06:27
You have to open the workbooks and link to the range.
Excelchat Expert 06/08/2018 - 06:29
=if(iferror(MATCH(A1,Sheet2!A:A,0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 06:29
=if(iferror(MATCH(A1,Sheet2!A:A,0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 06:29
Please check the formula.
User 06/08/2018 - 06:29
but is it possible to just do it for the entire column?
Excelchat Expert 06/08/2018 - 06:29
When you are selecting Sheet2 A column in the formula, please select it from other workbook.
Excelchat Expert 06/08/2018 - 06:30
You can check it for one cell and copy down the formula for all the rows.
User 06/08/2018 - 06:30
so where do i enter the fomula?
Excelchat Expert 06/08/2018 - 06:31
Please enter in the column B. Where is your data located?
User 06/08/2018 - 06:31
oh wait
Excelchat Expert 06/08/2018 - 06:31
I have given an example, where the data is present in column A
Excelchat Expert 06/08/2018 - 06:31
If it is different column, it need to be changed accordingly.
User 06/08/2018 - 06:32
its in column f in one sheet and coiumn a in the next and the yes would go in column d
Excelchat Expert 06/08/2018 - 06:34
Ok. You want to check the value in column F and place the result in column D?
Excelchat Expert 06/08/2018 - 06:34
=if(iferror(MATCH(F1,Sheet2!A:A,0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 06:34
Add this formula in column D1.
Excelchat Expert 06/08/2018 - 06:34
And please check the sheet2 (that is sheet name).
User 06/08/2018 - 06:34
i need to check and see if column f in one workbook match column a in the second workbook and then have that yes go in column d of the second workbook
Excelchat Expert 06/08/2018 - 06:36
=if(iferror(MATCH(A1,[workbook name]Sheet2!F:F,0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 06:37
After writing the formula in column D in second workbook, Please remove [workbook]sheet2F:F, and select the workobook and select column F.
Excelchat Expert 06/08/2018 - 06:38
[workbook name]Sheet2!F:F. In this add file name instead of workbook name and change the sheet name instead of sheet3.
Excelchat Expert 06/08/2018 - 06:39
.
[Uploaded an Excel file]
Excelchat Expert 06/08/2018 - 06:39
Please check for example, how the formula is specified.
User 06/08/2018 - 06:42
ok i am having trouble
Excelchat Expert 06/08/2018 - 06:42
Did you open the second workbook?
Excelchat Expert 06/08/2018 - 06:42
And what is the file name?
Excelchat Expert 06/08/2018 - 06:42
What is the tab name?
User 06/08/2018 - 06:42
is is possible to give you google doc links?
Excelchat Expert 06/08/2018 - 06:43
What is Google Doc Links?
Excelchat Expert 06/08/2018 - 06:43
You are looking for solution dealing with 2 workbooks?
User 06/08/2018 - 06:45
the first workbook is called FS18: San Francisco Registration and the tab is called registrants and on column f we need to see if that value matches the second workbook's column A then if they match we put the yes in column d of the second workbook
User 06/08/2018 - 06:47
yes i am looking for a solution dealing with 2 workbooks
Excelchat Expert 06/08/2018 - 06:47
=if(iferror(MATCH(A1,'[FS18: San Francisco Registration.xlsx]registrants'!F:F,0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 06:48
As, there are spaces in the file name, i have added single quotes.
Excelchat Expert 06/08/2018 - 06:48
Please check single colours Before [FS18
Excelchat Expert 06/08/2018 - 06:48
and after registration.
Excelchat Expert 06/08/2018 - 06:49
,'[FS18: San Francisco Registration.xlsx]registrants'!F:F,
Excelchat Expert 06/08/2018 - 06:49
I hope, the file extension is xlsx. If it is csv or xlsm or xls. Please change the format accordingly.
User 06/08/2018 - 06:50
it is in a web based spreadsheet
Excelchat Expert 06/08/2018 - 06:50
Is it in google sheets?
User 06/08/2018 - 06:50
yes
User 06/08/2018 - 06:51
is it possible to share my google sheet links with you then you can insert the forumla?
Excelchat Expert 06/08/2018 - 06:51
I am sorry, it is difficult to deal with it. As, i don' have spreadsheet link, etc.
Excelchat Expert 06/08/2018 - 06:51
Please share the links.
User 06/08/2018 - 06:52
https://docs.google.com/spreadsheets/d/1yDb4P6d9zIHGr-IBhgVfkqLaO2vKkVjYkGWqRXvYe9Q/edit?ts=5b62d331#gid=639680768
User 06/08/2018 - 06:52
https://docs.google.com/spreadsheets/d/1XnLcCs5QPuWkLhExlRJcm74LX5S5lR2TvjS7LYJUU4o/edit?ts=5b62d306#gid=0
Excelchat Expert 06/08/2018 - 06:52
it does not have access.
Excelchat Expert 06/08/2018 - 06:52
As per the policies, we can't work on such type of files.
Excelchat Expert 06/08/2018 - 06:53
We can't share personal info.
User 06/08/2018 - 06:53
ok i only have acess to view and not share
Excelchat Expert 06/08/2018 - 06:53
Or you have to make sure, you are sharing to general.
User 06/08/2018 - 06:53
https://docs.google.com/spreadsheets/d/1yDb4P6d9zIHGr-IBhgVfkqLaO2vKkVjYkGWqRXvYe9Q/edit?usp=sharing
User 06/08/2018 - 06:54
https://docs.google.com/spreadsheets/d/1XnLcCs5QPuWkLhExlRJcm74LX5S5lR2TvjS7LYJUU4o/edit?usp=sharing
User 06/08/2018 - 06:54
i think those should work
Excelchat Expert 06/08/2018 - 06:54
I have same problem.
Excelchat Expert 06/08/2018 - 06:54
It says access denied.
User 06/08/2018 - 06:54
ok
User 06/08/2018 - 06:55
then im not allowed to share
Excelchat Expert 06/08/2018 - 06:55
Ok. Is it from Office?
Excelchat Expert 06/08/2018 - 06:55
May be there could be the case.
Excelchat Expert 06/08/2018 - 06:55
Unfortunately, we are not allowed to share the file.
Excelchat Expert 06/08/2018 - 06:56
However, you have to use importrange function.
Excelchat Expert 06/08/2018 - 06:56
Pleas check importrange function and replace the range you want to check.
Excelchat Expert 06/08/2018 - 06:56
In Import range, there are 2 arguments.
User 06/08/2018 - 06:57
sorry im very confused
User 06/08/2018 - 06:57
i dont understand
Excelchat Expert 06/08/2018 - 06:57
In Google sheets, you have to use import range instead of workbook part in the formula provided.
Excelchat Expert 06/08/2018 - 06:58
Please type =Importrange("
User 06/08/2018 - 06:58
wait please
User 06/08/2018 - 06:58
https://docs.google.com/spreadsheets/d/1b0TSpdoiPo24o7fmyu8_CBqGhzGUE3gwLLbihEqFFc0/edit?usp=sharing
Excelchat Expert 06/08/2018 - 06:58
It guides to get the range.
Excelchat Expert 06/08/2018 - 06:59
I can see the file with view only permission.
User 06/08/2018 - 07:00
https://docs.google.com/spreadsheets/d/10gb7b8DC6mYutNU_dizG_Qr81ps8DQ39XIxY5z5CENw/edit?usp=sharing
Excelchat Expert 06/08/2018 - 07:01
I can see view only permissions.
Excelchat Expert 06/08/2018 - 07:01
Please enable to update the formula.
User 06/08/2018 - 07:01
ok so in the the fs 18 workbook i need to see if column f matches column a in the nr500 workbook and if it does have the yes go in to column d of the nr500 workbook
Excelchat Expert 06/08/2018 - 07:02
Ok.
User 06/08/2018 - 07:02
you should be able to update the forumla now
Excelchat Expert 06/08/2018 - 07:04
I have same problem.
Excelchat Expert 06/08/2018 - 07:04
However, i will try writing the formula here.
Excelchat Expert 06/08/2018 - 07:06
=if(iferror(MATCH(A1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1b0TSpdoiPo24o7fmyu8_CBqGhzGUE3gwLLbihEqFFc0/edit#gid=0","Sheet1!F:F"),0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 07:07
=if(iferror(MATCH(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1b0TSpdoiPo24o7fmyu8_CBqGhzGUE3gwLLbihEqFFc0/edit#gid=0","Sheet1!F:F"),0),0)>0,"Yes","No")
Excelchat Expert 06/08/2018 - 07:07
Please copy this formula in D2 cell.
User 06/08/2018 - 07:08
but it is only comparing 1 cell right? i want to apply it across the entire columns
Excelchat Expert 06/08/2018 - 07:08
Please copy down the cell.
Excelchat Expert 06/08/2018 - 07:09
Select the cell. Copy it using ctrl+C
Excelchat Expert 06/08/2018 - 07:10
Select other bottom cells from D3 onwards and paste it using ctrl+V
User 06/08/2018 - 07:11
well some of these should be marked as yes
Excelchat Expert 06/08/2018 - 07:12
Please give an example, where it is matching.
User 06/08/2018 - 07:14
oh i see
User 06/08/2018 - 07:14
some of the company names don't exactly match
Excelchat Expert 06/08/2018 - 07:15
Ok. Please check it when they match exactly.
User 06/08/2018 - 07:15
in one sheet it may be called credit karma inc and the other it is Credit Karma, Inc.
Excelchat Expert 06/08/2018 - 07:15
Ok. Got It. Please update the info to match them.
User 06/08/2018 - 07:16
now it works
User 06/08/2018 - 07:16
thank you so much for all of your help!
Excelchat Expert 06/08/2018 - 07:16
You are welcome.
Excelchat Expert 06/08/2018 - 07:16
Great to hear it.
Excelchat Expert 06/08/2018 - 07:16
Please visit Got IT Pro
User 06/08/2018 - 07:17
will just have to do some data clean up to ensure they all match
Excelchat Expert 06/08/2018 - 07:17
Finally, i could able to clear it.
Excelchat Expert 06/08/2018 - 07:17
Please leave a great rating after ending the session.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.