Match values of two columns with other two columns and return value in excel
up vote
0
down vote
favorite
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 at 5:14
Cœur
17k9102140
asked Dec 20 '16 at 11:26
NoviceStat
132
add a comment |
up vote
0
down vote
favorite
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 at 5:14
Cœur
17k9102140
asked Dec 20 '16 at 11:26
NoviceStat
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 |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 5:14
Cœur
17k9102140
asked Dec 20 '16 at 11:26
NoviceStat
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 at 5:14
Cœur
17k9102140
asked Dec 20 '16 at 11:26
NoviceStat
132
edited Nov 13 at 5:14
Cœur
17k9102140
asked Dec 20 '16 at 11:26
NoviceStat
132
edited Nov 13 at 5:14
Cœur
17k9102140
edited Nov 13 at 5:14
Cœur
17k9102140
edited Nov 13 at 5:14
Cœur
17k9102140
17k9102140
asked Dec 20 '16 at 11:26
NoviceStat
132
asked Dec 20 '16 at 11:26
NoviceStat
132
asked Dec 20 '16 at 11:26
NoviceStat
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
up vote
0
down vote
accepted
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 Wilkinson
2,79582552
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
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 Wilkinson
2,79582552
add a comment |
up vote
0
down vote
accepted
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 Wilkinson
2,79582552
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
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 Wilkinson
2,79582552
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 Wilkinson
2,79582552
answered Dec 20 '16 at 12:10
Tim Wilkinson
2,79582552
answered Dec 20 '16 at 12:10
Tim Wilkinson
2,79582552
answered Dec 20 '16 at 12:10
Tim Wilkinson
2,79582552
2,79582552
add a comment |
add a comment |
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