Match values of two columns with other two columns and return value in excel
I have two tables (Table A and Table B). Table A includes name of state and country number in first two columns and specific value corresponding it (column 3) (n=300). Table B also has name of state and country number with several repetition (n=5425). I want to match column 1 and column 2 of Table A with Column 1 and Column 2 of Table B and copy the value from column 3 of Table A in Column 3 of Table B in excel.
excel
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
add a comment |
I have two tables (Table A and Table B). Table A includes name of state and country number in first two columns and specific value corresponding it (column 3) (n=300). Table B also has name of state and country number with several repetition (n=5425). I want to match column 1 and column 2 of Table A with Column 1 and Column 2 of Table B and copy the value from column 3 of Table A in Column 3 of Table B in excel.
excel
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
1
If I understand correctly, "Table A" has only one record for each combination of state and country, while "Table B" has repetitions. If so, how do you want to select the third column of Table B according to the first two columns of Table A? You have a one-to-many relation. If my understanding is wrong, please clarify.
– FDavidov
Dec 20 '16 at 11:39
How do I format my posts using Markdown or HTML?
– buhtz
Dec 20 '16 at 11:48
@FDavidov Yes, Table A has unique record and Table B has reperations. I want to copy third column of Table A to to third column of Table B where records matches. Thus, I have one to one relation only. But I need to repeat where values are match. Hope this is clear now.
– NoviceStat
Dec 20 '16 at 11:52
OK, ok, I understood it the other way round. Will post an answer in a couple of minutes. Stay tuned...
– FDavidov
Dec 20 '16 at 11:54
Can you use any helper columns?
– Tim Wilkinson
Dec 20 '16 at 12:01
add a comment |
I have two tables (Table A and Table B). Table A includes name of state and country number in first two columns and specific value corresponding it (column 3) (n=300). Table B also has name of state and country number with several repetition (n=5425). I want to match column 1 and column 2 of Table A with Column 1 and Column 2 of Table B and copy the value from column 3 of Table A in Column 3 of Table B in excel.
excel
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
I have two tables (Table A and Table B). Table A includes name of state and country number in first two columns and specific value corresponding it (column 3) (n=300). Table B also has name of state and country number with several repetition (n=5425). I want to match column 1 and column 2 of Table A with Column 1 and Column 2 of Table B and copy the value from column 3 of Table A in Column 3 of Table B in excel.
excel
excel
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
edited Nov 13 '18 at 5:14
Cœur
17.7k9106145
17.7k9106145
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
asked Dec 20 '16 at 11:26
NoviceStatNoviceStat
132
132
1
If I understand correctly, "Table A" has only one record for each combination of state and country, while "Table B" has repetitions. If so, how do you want to select the third column of Table B according to the first two columns of Table A? You have a one-to-many relation. If my understanding is wrong, please clarify.
– FDavidov
Dec 20 '16 at 11:39
How do I format my posts using Markdown or HTML?
– buhtz
Dec 20 '16 at 11:48
@FDavidov Yes, Table A has unique record and Table B has reperations. I want to copy third column of Table A to to third column of Table B where records matches. Thus, I have one to one relation only. But I need to repeat where values are match. Hope this is clear now.
– NoviceStat
Dec 20 '16 at 11:52
OK, ok, I understood it the other way round. Will post an answer in a couple of minutes. Stay tuned...
– FDavidov
Dec 20 '16 at 11:54
Can you use any helper columns?
– Tim Wilkinson
Dec 20 '16 at 12:01
add a comment |
1
If I understand correctly, "Table A" has only one record for each combination of state and country, while "Table B" has repetitions. If so, how do you want to select the third column of Table B according to the first two columns of Table A? You have a one-to-many relation. If my understanding is wrong, please clarify.
– FDavidov
Dec 20 '16 at 11:39
How do I format my posts using Markdown or HTML?
– buhtz
Dec 20 '16 at 11:48
@FDavidov Yes, Table A has unique record and Table B has reperations. I want to copy third column of Table A to to third column of Table B where records matches. Thus, I have one to one relation only. But I need to repeat where values are match. Hope this is clear now.
– NoviceStat
Dec 20 '16 at 11:52
OK, ok, I understood it the other way round. Will post an answer in a couple of minutes. Stay tuned...
– FDavidov
Dec 20 '16 at 11:54
Can you use any helper columns?
– Tim Wilkinson
Dec 20 '16 at 12:01
1
1
If I understand correctly, "Table A" has only one record for each combination of state and country, while "Table B" has repetitions. If so, how do you want to select the third column of Table B according to the first two columns of Table A? You have a one-to-many relation. If my understanding is wrong, please clarify.
– FDavidov
Dec 20 '16 at 11:39
If I understand correctly, "Table A" has only one record for each combination of state and country, while "Table B" has repetitions. If so, how do you want to select the third column of Table B according to the first two columns of Table A? You have a one-to-many relation. If my understanding is wrong, please clarify.
– FDavidov
Dec 20 '16 at 11:39
How do I format my posts using Markdown or HTML?
– buhtz
Dec 20 '16 at 11:48
How do I format my posts using Markdown or HTML?
– buhtz
Dec 20 '16 at 11:48
@FDavidov Yes, Table A has unique record and Table B has reperations. I want to copy third column of Table A to to third column of Table B where records matches. Thus, I have one to one relation only. But I need to repeat where values are match. Hope this is clear now.
– NoviceStat
Dec 20 '16 at 11:52
@FDavidov Yes, Table A has unique record and Table B has reperations. I want to copy third column of Table A to to third column of Table B where records matches. Thus, I have one to one relation only. But I need to repeat where values are match. Hope this is clear now.
– NoviceStat
Dec 20 '16 at 11:52
OK, ok, I understood it the other way round. Will post an answer in a couple of minutes. Stay tuned...
– FDavidov
Dec 20 '16 at 11:54
OK, ok, I understood it the other way round. Will post an answer in a couple of minutes. Stay tuned...
– FDavidov
Dec 20 '16 at 11:54
Can you use any helper columns?
– Tim Wilkinson
Dec 20 '16 at 12:01
Can you use any helper columns?
– Tim Wilkinson
Dec 20 '16 at 12:01
add a comment |
1 Answer
1
active
oldest
votes
If you are able to use a helper column, in Table A Column D put =A1&B1 if its an actual table it should autopopulate, otherwise fill down for all your data), then using INDEX MATCH in Table B, put the following in C1
=INDEX(Sheet1!C:C, MATCH(Sheet2!A1&Sheet2!B1, Sheet1!D:D, 0))
Alternatively you can match multiple criteria with an array formula, so use the following,
=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!B1,Sheet1!A:A&Sheet1!B:B,0))
And enter by hitting CTRL + SHIFT + ENTER.
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
add a comment |
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%2f41241265%2fmatch-values-of-two-columns-with-other-two-columns-and-return-value-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you are able to use a helper column, in Table A Column D put =A1&B1 if its an actual table it should autopopulate, otherwise fill down for all your data), then using INDEX MATCH in Table B, put the following in C1
=INDEX(Sheet1!C:C, MATCH(Sheet2!A1&Sheet2!B1, Sheet1!D:D, 0))
Alternatively you can match multiple criteria with an array formula, so use the following,
=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!B1,Sheet1!A:A&Sheet1!B:B,0))
And enter by hitting CTRL + SHIFT + ENTER.
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
add a comment |
If you are able to use a helper column, in Table A Column D put =A1&B1 if its an actual table it should autopopulate, otherwise fill down for all your data), then using INDEX MATCH in Table B, put the following in C1
=INDEX(Sheet1!C:C, MATCH(Sheet2!A1&Sheet2!B1, Sheet1!D:D, 0))
Alternatively you can match multiple criteria with an array formula, so use the following,
=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!B1,Sheet1!A:A&Sheet1!B:B,0))
And enter by hitting CTRL + SHIFT + ENTER.
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
add a comment |
If you are able to use a helper column, in Table A Column D put =A1&B1 if its an actual table it should autopopulate, otherwise fill down for all your data), then using INDEX MATCH in Table B, put the following in C1
=INDEX(Sheet1!C:C, MATCH(Sheet2!A1&Sheet2!B1, Sheet1!D:D, 0))
Alternatively you can match multiple criteria with an array formula, so use the following,
=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!B1,Sheet1!A:A&Sheet1!B:B,0))
And enter by hitting CTRL + SHIFT + ENTER.
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
If you are able to use a helper column, in Table A Column D put =A1&B1 if its an actual table it should autopopulate, otherwise fill down for all your data), then using INDEX MATCH in Table B, put the following in C1
=INDEX(Sheet1!C:C, MATCH(Sheet2!A1&Sheet2!B1, Sheet1!D:D, 0))
Alternatively you can match multiple criteria with an array formula, so use the following,
=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!B1,Sheet1!A:A&Sheet1!B:B,0))
And enter by hitting CTRL + SHIFT + ENTER.
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
answered Dec 20 '16 at 12:10
Tim WilkinsonTim Wilkinson
2,83082553
2,83082553
add a comment |
add a comment |
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%2f41241265%2fmatch-values-of-two-columns-with-other-two-columns-and-return-value-in-excel%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
1
If I understand correctly, "Table A" has only one record for each combination of state and country, while "Table B" has repetitions. If so, how do you want to select the third column of Table B according to the first two columns of Table A? You have a one-to-many relation. If my understanding is wrong, please clarify.
– FDavidov
Dec 20 '16 at 11:39
How do I format my posts using Markdown or HTML?
– buhtz
Dec 20 '16 at 11:48
@FDavidov Yes, Table A has unique record and Table B has reperations. I want to copy third column of Table A to to third column of Table B where records matches. Thus, I have one to one relation only. But I need to repeat where values are match. Hope this is clear now.
– NoviceStat
Dec 20 '16 at 11:52
OK, ok, I understood it the other way round. Will post an answer in a couple of minutes. Stay tuned...
– FDavidov
Dec 20 '16 at 11:54
Can you use any helper columns?
– Tim Wilkinson
Dec 20 '16 at 12:01