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.
