Excel Match Email With another Sheet, Return Another Cell2019 Community Moderator ElectionExcel - How to Delete All rows from 1 sheet that do not contain column input from another sheetcomparing email id usernames of two columns in excel and printing only username in third columnExcel: combine columns in different sheets matching informationsHow can I rewrite an Excel formula to exclude users with an @mydomain.com in their email address?return value of a Cell if another cell is matched in the same rowFilter Out Blank Value Cells on Another Sheet In ExcelExcel Information from sheet 1 to automatically filter to multiple other sheetsExcel dynamic list with no blank cellsExcel - How to highlight cells that match a listing in another sheet?EXCEL formulas - return true if the same value in one of three other columns
Plywood subfloor won't screw down in a trailer home
Time dilation for a moving electronic clock
Sword in the Stone story where the sword was held in place by electromagnets
Can "semicircle" be used to refer to a part-circle that is not a exact half-circle?
Rejected in 4th interview round citing insufficient years of experience
Replacing Windows 7 security updates with anti-virus?
Why doesn't the EU now just force the UK to choose between referendum and no-deal?
What exactly is the purpose of connection links straped between the rocket and the launch pad
Should we release the security issues we found in our product as CVE or we can just update those on weekly release notes?
Should QA ask requirements to developers?
What has been your most complicated TikZ drawing?
What happens with multiple copies of Humility and Glorious Anthem on the battlefield?
Can the druid cantrip Thorn Whip really defeat a water weird this easily?
Force user to remove USB token
Is a lawful good "antagonist" effective?
If Invisibility ends because the original caster casts a non-concentration spell, does Invisibility also end on other targets of the original casting?
Am I not good enough for you?
Do f-stop and exposure time perfectly cancel?
Straight line with arrows and dots
Why would a jet engine that runs at temps excess of 2000°C burn when it crashes?
Make a transparent 448*448 image
What is the difference between "shut" and "close"?
Word for a person who has no opinion about whether god exists
Unreachable code, but reachable with exception
Excel Match Email With another Sheet, Return Another Cell
2019 Community Moderator ElectionExcel - How to Delete All rows from 1 sheet that do not contain column input from another sheetcomparing email id usernames of two columns in excel and printing only username in third columnExcel: combine columns in different sheets matching informationsHow can I rewrite an Excel formula to exclude users with an @mydomain.com in their email address?return value of a Cell if another cell is matched in the same rowFilter Out Blank Value Cells on Another Sheet In ExcelExcel Information from sheet 1 to automatically filter to multiple other sheetsExcel dynamic list with no blank cellsExcel - How to highlight cells that match a listing in another sheet?EXCEL formulas - return true if the same value in one of three other columns
I am currently working on an excel workbook that is pulling in a list of roughly 1000 reports going to a bunch of different emails. It is setup as Sheet: "ReportValues" Column A being Report Titles and Columns B-Q having all the different emails that the reports are sent to. I am trying to make a new sheet that lists the emails and reports they receive. Currently it is setup as Sheet: "Emails" Column A being the list of emails, Column B verifying if the emails are still in the contacts group, with the hopes of setting up Column C-Z as a listing of reports that email receives. If it is just a list of reports going to the email in one column that would work fine as well. I have tried a few different formulas but for the most part they have been unsuccessful. One side note that may help with this is that on the "ReportValues" table, some of the columns that should contain emails are empty because some reports only go to five emails while some go to seven and others go to three.
This may be simple to some but I am unexperienced with formulas in Excel so any help is greatly appreciated
My first try: "=INDEX(ReportValues!$B$1:$Q$1000,QUOTIENT(ROW(A2)-1,16)+1,MOD(ROW(A2)-1,16)+1) which returns a 0 even with B1 of ReportValues having an email in the box.
My second try: =INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!B$2:B$1000,0)) which gave me the first report for the email in Column A but wouldn't work other than giving the first report it found.
My Third try: =IFERROR( SMALL( IF( $A$2:$A$1000 = TRANSPOSE(ReportValues!$B$2:$Q$1000), 1 + ROW( $A$2:$A$1000 ) - ROW($A$2 ), "" ), ROWS($2:2 ) ), "") which returned a 1.
Example of ReportValues Table:
Column A | Column B | Column C | Column D |
Report Title | Dest Detail 1 | Dest Detail 2 | Dest Detail 3 |
Report One | Emailone@email.com | Emailtwo@email.com | |
Report Two | Emailtwo@email.com | | |
Report Three | Emailthree@email.com | Emailone@email.com | Emailfour@email.com |
Example of Emails Table
Column A | Column B | Column C | Column D |
Email List | Still Employeed? | Report Title | Report Title 2 |
Emailone@email.com | True | Report One | Report Three |
Emailtwo@email.com | True | Report One | Report Two |
Emailthree@email.com | False | Report Three | |
Emailfour@email.com | True | Report Three | |
excel excel-formula
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
asked Mar 6 at 17:40
AcurzeAcurze
135
|
show 2 more comments
I am currently working on an excel workbook that is pulling in a list of roughly 1000 reports going to a bunch of different emails. It is setup as Sheet: "ReportValues" Column A being Report Titles and Columns B-Q having all the different emails that the reports are sent to. I am trying to make a new sheet that lists the emails and reports they receive. Currently it is setup as Sheet: "Emails" Column A being the list of emails, Column B verifying if the emails are still in the contacts group, with the hopes of setting up Column C-Z as a listing of reports that email receives. If it is just a list of reports going to the email in one column that would work fine as well. I have tried a few different formulas but for the most part they have been unsuccessful. One side note that may help with this is that on the "ReportValues" table, some of the columns that should contain emails are empty because some reports only go to five emails while some go to seven and others go to three.
This may be simple to some but I am unexperienced with formulas in Excel so any help is greatly appreciated
My first try: "=INDEX(ReportValues!$B$1:$Q$1000,QUOTIENT(ROW(A2)-1,16)+1,MOD(ROW(A2)-1,16)+1) which returns a 0 even with B1 of ReportValues having an email in the box.
My second try: =INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!B$2:B$1000,0)) which gave me the first report for the email in Column A but wouldn't work other than giving the first report it found.
My Third try: =IFERROR( SMALL( IF( $A$2:$A$1000 = TRANSPOSE(ReportValues!$B$2:$Q$1000), 1 + ROW( $A$2:$A$1000 ) - ROW($A$2 ), "" ), ROWS($2:2 ) ), "") which returned a 1.
Example of ReportValues Table:
Column A | Column B | Column C | Column D |
Report Title | Dest Detail 1 | Dest Detail 2 | Dest Detail 3 |
Report One | Emailone@email.com | Emailtwo@email.com | |
Report Two | Emailtwo@email.com | | |
Report Three | Emailthree@email.com | Emailone@email.com | Emailfour@email.com |
Example of Emails Table
Column A | Column B | Column C | Column D |
Email List | Still Employeed? | Report Title | Report Title 2 |
Emailone@email.com | True | Report One | Report Three |
Emailtwo@email.com | True | Report One | Report Two |
Emailthree@email.com | False | Report Three | |
Emailfour@email.com | True | Report Three | |
excel excel-formula
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
asked Mar 6 at 17:40
AcurzeAcurze
135
Is each e-mail in it's own cell? Can you provide a small set of mock data? (say 3 reports and 4-5 e-mails)
– cybernetic.nomad
Mar 6 at 18:00
@cybernetic.nomad I added example tables
– Acurze
Mar 6 at 18:13
=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!D$2:D$1000,0))seems to work but only for one match and doesn't work if I switch to Q$1000.
– Acurze
Mar 6 at 19:38
=TEXTJOIN(", ",TRUE,IF(A1=ReportValues!$B$1:$Q$1000,ReportValues!$A$2:$A$1000,"")) Didn't seem to work either
– Acurze
Mar 6 at 20:51
You may want to consider cleaning up your table before trying to extract info out of it
– cybernetic.nomad
Mar 6 at 21:10
|
show 2 more comments
I am currently working on an excel workbook that is pulling in a list of roughly 1000 reports going to a bunch of different emails. It is setup as Sheet: "ReportValues" Column A being Report Titles and Columns B-Q having all the different emails that the reports are sent to. I am trying to make a new sheet that lists the emails and reports they receive. Currently it is setup as Sheet: "Emails" Column A being the list of emails, Column B verifying if the emails are still in the contacts group, with the hopes of setting up Column C-Z as a listing of reports that email receives. If it is just a list of reports going to the email in one column that would work fine as well. I have tried a few different formulas but for the most part they have been unsuccessful. One side note that may help with this is that on the "ReportValues" table, some of the columns that should contain emails are empty because some reports only go to five emails while some go to seven and others go to three.
This may be simple to some but I am unexperienced with formulas in Excel so any help is greatly appreciated
My first try: "=INDEX(ReportValues!$B$1:$Q$1000,QUOTIENT(ROW(A2)-1,16)+1,MOD(ROW(A2)-1,16)+1) which returns a 0 even with B1 of ReportValues having an email in the box.
My second try: =INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!B$2:B$1000,0)) which gave me the first report for the email in Column A but wouldn't work other than giving the first report it found.
My Third try: =IFERROR( SMALL( IF( $A$2:$A$1000 = TRANSPOSE(ReportValues!$B$2:$Q$1000), 1 + ROW( $A$2:$A$1000 ) - ROW($A$2 ), "" ), ROWS($2:2 ) ), "") which returned a 1.
Example of ReportValues Table:
Column A | Column B | Column C | Column D |
Report Title | Dest Detail 1 | Dest Detail 2 | Dest Detail 3 |
Report One | Emailone@email.com | Emailtwo@email.com | |
Report Two | Emailtwo@email.com | | |
Report Three | Emailthree@email.com | Emailone@email.com | Emailfour@email.com |
Example of Emails Table
Column A | Column B | Column C | Column D |
Email List | Still Employeed? | Report Title | Report Title 2 |
Emailone@email.com | True | Report One | Report Three |
Emailtwo@email.com | True | Report One | Report Two |
Emailthree@email.com | False | Report Three | |
Emailfour@email.com | True | Report Three | |
excel excel-formula
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
asked Mar 6 at 17:40
AcurzeAcurze
135
I am currently working on an excel workbook that is pulling in a list of roughly 1000 reports going to a bunch of different emails. It is setup as Sheet: "ReportValues" Column A being Report Titles and Columns B-Q having all the different emails that the reports are sent to. I am trying to make a new sheet that lists the emails and reports they receive. Currently it is setup as Sheet: "Emails" Column A being the list of emails, Column B verifying if the emails are still in the contacts group, with the hopes of setting up Column C-Z as a listing of reports that email receives. If it is just a list of reports going to the email in one column that would work fine as well. I have tried a few different formulas but for the most part they have been unsuccessful. One side note that may help with this is that on the "ReportValues" table, some of the columns that should contain emails are empty because some reports only go to five emails while some go to seven and others go to three.
This may be simple to some but I am unexperienced with formulas in Excel so any help is greatly appreciated
My first try: "=INDEX(ReportValues!$B$1:$Q$1000,QUOTIENT(ROW(A2)-1,16)+1,MOD(ROW(A2)-1,16)+1) which returns a 0 even with B1 of ReportValues having an email in the box.
My second try: =INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!B$2:B$1000,0)) which gave me the first report for the email in Column A but wouldn't work other than giving the first report it found.
My Third try: =IFERROR( SMALL( IF( $A$2:$A$1000 = TRANSPOSE(ReportValues!$B$2:$Q$1000), 1 + ROW( $A$2:$A$1000 ) - ROW($A$2 ), "" ), ROWS($2:2 ) ), "") which returned a 1.
Example of ReportValues Table:
Column A | Column B | Column C | Column D |
Report Title | Dest Detail 1 | Dest Detail 2 | Dest Detail 3 |
Report One | Emailone@email.com | Emailtwo@email.com | |
Report Two | Emailtwo@email.com | | |
Report Three | Emailthree@email.com | Emailone@email.com | Emailfour@email.com |
Example of Emails Table
Column A | Column B | Column C | Column D |
Email List | Still Employeed? | Report Title | Report Title 2 |
Emailone@email.com | True | Report One | Report Three |
Emailtwo@email.com | True | Report One | Report Two |
Emailthree@email.com | False | Report Three | |
Emailfour@email.com | True | Report Three | |
excel excel-formula
excel excel-formula
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
asked Mar 6 at 17:40
AcurzeAcurze
135
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
asked Mar 6 at 17:40
AcurzeAcurze
135
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
edited Mar 6 at 18:39
cybernetic.nomad
2,68621021
2,68621021
asked Mar 6 at 17:40
AcurzeAcurze
135
asked Mar 6 at 17:40
AcurzeAcurze
135
asked Mar 6 at 17:40
AcurzeAcurze
135
135
Is each e-mail in it's own cell? Can you provide a small set of mock data? (say 3 reports and 4-5 e-mails)
– cybernetic.nomad
Mar 6 at 18:00
@cybernetic.nomad I added example tables
– Acurze
Mar 6 at 18:13
=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!D$2:D$1000,0))seems to work but only for one match and doesn't work if I switch to Q$1000.
– Acurze
Mar 6 at 19:38
=TEXTJOIN(", ",TRUE,IF(A1=ReportValues!$B$1:$Q$1000,ReportValues!$A$2:$A$1000,"")) Didn't seem to work either
– Acurze
Mar 6 at 20:51
You may want to consider cleaning up your table before trying to extract info out of it
– cybernetic.nomad
Mar 6 at 21:10
|
show 2 more comments
Is each e-mail in it's own cell? Can you provide a small set of mock data? (say 3 reports and 4-5 e-mails)
– cybernetic.nomad
Mar 6 at 18:00
@cybernetic.nomad I added example tables
– Acurze
Mar 6 at 18:13
=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!D$2:D$1000,0))seems to work but only for one match and doesn't work if I switch to Q$1000.
– Acurze
Mar 6 at 19:38
=TEXTJOIN(", ",TRUE,IF(A1=ReportValues!$B$1:$Q$1000,ReportValues!$A$2:$A$1000,"")) Didn't seem to work either
– Acurze
Mar 6 at 20:51
You may want to consider cleaning up your table before trying to extract info out of it
– cybernetic.nomad
Mar 6 at 21:10
Is each e-mail in it's own cell? Can you provide a small set of mock data? (say 3 reports and 4-5 e-mails)
– cybernetic.nomad
Mar 6 at 18:00
Is each e-mail in it's own cell? Can you provide a small set of mock data? (say 3 reports and 4-5 e-mails)
– cybernetic.nomad
Mar 6 at 18:00
@cybernetic.nomad I added example tables
– Acurze
Mar 6 at 18:13
@cybernetic.nomad I added example tables
– Acurze
Mar 6 at 18:13
=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!D$2:D$1000,0)) seems to work but only for one match and doesn't work if I switch to Q$1000.– Acurze
Mar 6 at 19:38
=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!D$2:D$1000,0)) seems to work but only for one match and doesn't work if I switch to Q$1000.– Acurze
Mar 6 at 19:38
=TEXTJOIN(", ",TRUE,IF(A1=ReportValues!$B$1:$Q$1000,ReportValues!$A$2:$A$1000,"")) Didn't seem to work either
– Acurze
Mar 6 at 20:51
=TEXTJOIN(", ",TRUE,IF(A1=ReportValues!$B$1:$Q$1000,ReportValues!$A$2:$A$1000,"")) Didn't seem to work either
– Acurze
Mar 6 at 20:51
You may want to consider cleaning up your table before trying to extract info out of it
– cybernetic.nomad
Mar 6 at 21:10
You may want to consider cleaning up your table before trying to extract info out of it
– cybernetic.nomad
Mar 6 at 21:10
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55029171%2fexcel-match-email-with-another-sheet-return-another-cell%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55029171%2fexcel-match-email-with-another-sheet-return-another-cell%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is each e-mail in it's own cell? Can you provide a small set of mock data? (say 3 reports and 4-5 e-mails)
– cybernetic.nomad
Mar 6 at 18:00
@cybernetic.nomad I added example tables
– Acurze
Mar 6 at 18:13
=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!D$2:D$1000,0))seems to work but only for one match and doesn't work if I switch to Q$1000.– Acurze
Mar 6 at 19:38
=TEXTJOIN(", ",TRUE,IF(A1=ReportValues!$B$1:$Q$1000,ReportValues!$A$2:$A$1000,"")) Didn't seem to work either
– Acurze
Mar 6 at 20:51
You may want to consider cleaning up your table before trying to extract info out of it
– cybernetic.nomad
Mar 6 at 21:10