Question description:
This user has given permission to use the problem statement for this
blog.
I have two columns with names and a third one with email addresses. For every name that matches, I need to copy the email address to an empty column next to the matching name. .
Solved by E. U. in 15 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/10/2018 - 08:22
Welcome to ExcelChat!
Excelchat Expert
16/10/2018 - 08:22
I see that your question is about matching the data. I will help you with the problem and explain the solution afterwards.
Excelchat Expert
16/10/2018 - 08:22
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
User
16/10/2018 - 08:22
Thank you!
Excelchat Expert
16/10/2018 - 08:22
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
16/10/2018 - 08:22
Do you want to share a document where you have a problem?
User
16/10/2018 - 08:22
No!
Excelchat Expert
16/10/2018 - 08:23
Okay, could you please share the sample of your data on the sheet near the chat, so we could make a solution based on it?
User
16/10/2018 - 08:24
Sure
Excelchat Expert
16/10/2018 - 08:26
Okay, so in this example, you would like to have the result in the column near the table A:C, right?
Excelchat Expert
16/10/2018 - 08:27
It is enough for the sample, thank you
Excelchat Expert
16/10/2018 - 08:27
Could you please show and explain the expected result?
User
16/10/2018 - 08:28
angelajacksonhorse@1.com Angela Jackson Angela Jackson
chachaangelina@2.com Woodhull Angelina Summers
tiniestbubble@3.net Angeline Anderson Angelo Gentile
anilvidhya@4.com Anil Ithikkat Anil Ithikkat
anilvidhya@5.com Anil Ithikkat Anita Frankenberger
teensief@12.com Anita Frankenberger Ann Bujnowski
dixieazc@42.net Ann Z Carter Ann Z Carter
apurna108@102.com Anna Johansson Anna Johansson
gangamayee@47.com Annada Clarke Anna Kruszewska
OK!
User
16/10/2018 - 08:28
Column C has the correct name I am looking for
User
16/10/2018 - 08:29
If the name on column B matches the name on column C, I want to copy the email from column A to column D
Excelchat Expert
16/10/2018 - 08:29
So for row 1, we need to do the following, right? If Name from B1 exists in column C, then we return the email from the same row, right?
User
16/10/2018 - 08:29
Correct!
Excelchat Expert
16/10/2018 - 08:29
If B1 exists in C2, we need to take email from A1 or A2?
User
16/10/2018 - 08:30
Not really. If B1 exists in C2 through C8, copy A1 to D1
User
16/10/2018 - 08:30
Sorry, C1 through C8
Excelchat Expert
16/10/2018 - 08:31
Okay, got it
Excelchat Expert
16/10/2018 - 08:31
Will make the solution in few minutes and explain that to you after that
Excelchat Expert
16/10/2018 - 08:32
Okay, your result is ready at column D
Excelchat Expert
16/10/2018 - 08:33
If the name from column B exists in column C, then we return the email from column A
User
16/10/2018 - 08:33
But I did not see it working on D8.
Excelchat Expert
16/10/2018 - 08:33
Annada Clarke doesn't exist in column C
User
16/10/2018 - 08:33
There is no match for D8 on column B
Excelchat Expert
16/10/2018 - 08:33
So for D8
Excelchat Expert
16/10/2018 - 08:33
It should check if B8 exists in C1:C8, right?
Excelchat Expert
16/10/2018 - 08:34
And if yes, we return A8, right?
User
16/10/2018 - 08:34
And it is giving me a positive result.
Excelchat Expert
16/10/2018 - 08:34
On our sheet the result is available only at D1,D6 and D7, since B1, B6 and B7 exist in column C
User
16/10/2018 - 08:34
I am actually referring to cell D6
Excelchat Expert
16/10/2018 - 08:35
It exists in C5
Excelchat Expert
16/10/2018 - 08:35
That is why we show the email from A6
Excelchat Expert
16/10/2018 - 08:35
Because B5 was found in columnC
User
16/10/2018 - 08:35
The result on D6 should actually be on D5
Excelchat Expert
16/10/2018 - 08:35
I mean B6 was found there
Excelchat Expert
16/10/2018 - 08:36
So in fact you need to check if C5 exist in column B?
Excelchat Expert
16/10/2018 - 08:36
And if yes, return email from that row?
User
16/10/2018 - 08:36
That is correct.
User
16/10/2018 - 08:36
Correct!
Excelchat Expert
16/10/2018 - 08:36
Okay, will remake it, few minutes please
Excelchat Expert
16/10/2018 - 08:37
Is it correct now?
Excelchat Expert
16/10/2018 - 08:39
So now in D1 we check if C1 exists in column B. If yes, we return the email related to the found position in column B
User
16/10/2018 - 08:39
4 is not wokring
Excelchat Expert
16/10/2018 - 08:39
It is working, it found the name in B4 and returned email from the same row
Excelchat Expert
16/10/2018 - 08:39
I see that you also have this name in B5
Excelchat Expert
16/10/2018 - 08:39
But Excel will find the first occurrence of the name
User
16/10/2018 - 08:40
It is working now.
User
16/10/2018 - 08:40
That is it.
User
16/10/2018 - 08:40
How much do I owe you?
Excelchat Expert
16/10/2018 - 08:41
You don't have to pay me, probably you are having a free session or an unlimited sessions plan
Excelchat Expert
16/10/2018 - 08:41
Do you have any questions about the solution?
Excelchat Expert
16/10/2018 - 08:41
The formula for D1 is:
Excelchat Expert
16/10/2018 - 08:41
=iferror(index(A:A,match(C1,B:B,0)),"")
Excelchat Expert
16/10/2018 - 08:41
So we try to get the data from column, based on the match of name from Column C in Column B
Excelchat Expert
16/10/2018 - 08:42
Index return an error function if it couldn't find the result, so if we get an error, we show nothing (double quotes, "")
User
16/10/2018 - 08:42
Give me a moment, please. I am trying it.
Excelchat Expert
16/10/2018 - 08:43
Here you can study the pricing:
Excelchat Expert
16/10/2018 - 08:43
https://www.got-it.ai/solutions/excel-chat/pricing/personal
User
16/10/2018 - 08:43
It seems that I need to create an index for this to work.
Excelchat Expert
16/10/2018 - 08:44
What do you mean by creating an index?
Excelchat Expert
16/10/2018 - 08:44
For this solution, we need to use the combination of Index + Match function, and IFERROR to handle the error results
Excelchat Expert
16/10/2018 - 08:44
Index allows to show the content from the specified range, and number of rows and column in it to show the cell
Excelchat Expert
16/10/2018 - 08:45
Match allows to find the position of the value in the range
Excelchat Expert
16/10/2018 - 08:45
IFERROR allows to show a custom output instead of error
User
16/10/2018 - 08:45
How do I create the index?
Excelchat Expert
16/10/2018 - 08:45
You just activate the cell where you need to have the formula
Excelchat Expert
16/10/2018 - 08:45
And type it there
Excelchat Expert
16/10/2018 - 08:45
For D1 the formula is
Excelchat Expert
16/10/2018 - 08:45
=iferror(index(A:A,match(C1,B:B,0)),"")
Excelchat Expert
16/10/2018 - 08:45
Then we just drag this formula down, and it works for all the range that we need
User
16/10/2018 - 08:46
I don't know how to create the index.
Excelchat Expert
16/10/2018 - 08:46
INDEX is just the name of the function
User
16/10/2018 - 08:47
OK!
Excelchat Expert
16/10/2018 - 08:47
You don't need to create it, you just write index with your keyboard
Excelchat Expert
16/10/2018 - 08:47
it is like a small coding
Excelchat Expert
16/10/2018 - 08:47
So in D1 we used the INDEX function, which means:
Excelchat Expert
16/10/2018 - 08:47
That we try to get the value from range A:A (column A, the required email)
Excelchat Expert
16/10/2018 - 08:48
In order to know which row do we need to take from this range, we use the MATCH function
Excelchat Expert
16/10/2018 - 08:48
With MATCH function, we find the position of C1 in the column B. Then we pass this value to the INDEX function, and it knows from which row do we need to have the result
Excelchat Expert
16/10/2018 - 08:48
In some cases we won't find the name from column C in column B
Excelchat Expert
16/10/2018 - 08:48
In this case we would see an error "#N/A"
Excelchat Expert
16/10/2018 - 08:49
In order to prevent it, we use the IFERROR function, which shows nothing instead of the error in this case
Excelchat Expert
16/10/2018 - 08:49
That is all the logic which we used to solve your problem
Excelchat Expert
16/10/2018 - 08:50
So when you will implement this solution in your real file:
Excelchat Expert
16/10/2018 - 08:50
=iferror(index(A:A,match(C1,B:B,0)),"")
Excelchat Expert
16/10/2018 - 08:50
A:A - the column with your emails
Excelchat Expert
16/10/2018 - 08:50
C1 - the cell where you have the name which needs to be checked if it exists in another column
Excelchat Expert
16/10/2018 - 08:50
B:B - the column where we check if C1 exists
Excelchat Expert
16/10/2018 - 08:50
Pretty simply
Excelchat Expert
16/10/2018 - 08:51
Please let me know what questions you might have about the solution
User
16/10/2018 - 08:51
It is working.,
Excelchat Expert
16/10/2018 - 08:51
Great to hear that!
Excelchat Expert
16/10/2018 - 08:53
Do you have some other questions about it?
Excelchat Expert
16/10/2018 - 08:57
It was a pleasure to assist you! If it happens that you have any Excel or Google Sheets problems in future, please don't hesitate to reach us again.
Excelchat Expert
16/10/2018 - 08:57
You may now end the session, I would highly appreciate if you rate our session at the end and leave some feedback. Have a nice day!
