index match instead using vlookup
I have two csv files.
First csv
Id Name Price Description
1 X 2 lalala
2 Y 4 nanana
3 Z 6 papapa
and another:
Id Description
2 here
6 here
1 here
I want to replace description from first csv file if ids are the same, if they are not then save the previous value from description from second csv. I want to this via index match function, what is the best way to do it?
excel match vlookup indexof
asked Nov 24 '18 at 20:54
dokidoki
589
add a comment |
I have two csv files.
First csv
Id Name Price Description
1 X 2 lalala
2 Y 4 nanana
3 Z 6 papapa
and another:
Id Description
2 here
6 here
1 here
I want to replace description from first csv file if ids are the same, if they are not then save the previous value from description from second csv. I want to this via index match function, what is the best way to do it?
excel match vlookup indexof
asked Nov 24 '18 at 20:54
dokidoki
589
1
Index will use the target range ie column, match uses the lookup range - have a look at the vlookup its index column and result column.
– Solar Mike
Nov 24 '18 at 21:32
There are examples of the use of index and match on this site - worth looking for...
– Solar Mike
Nov 25 '18 at 6:41
add a comment |
I have two csv files.
First csv
Id Name Price Description
1 X 2 lalala
2 Y 4 nanana
3 Z 6 papapa
and another:
Id Description
2 here
6 here
1 here
I want to replace description from first csv file if ids are the same, if they are not then save the previous value from description from second csv. I want to this via index match function, what is the best way to do it?
excel match vlookup indexof
asked Nov 24 '18 at 20:54
dokidoki
589
I have two csv files.
First csv
Id Name Price Description
1 X 2 lalala
2 Y 4 nanana
3 Z 6 papapa
and another:
Id Description
2 here
6 here
1 here
I want to replace description from first csv file if ids are the same, if they are not then save the previous value from description from second csv. I want to this via index match function, what is the best way to do it?
excel match vlookup indexof
excel match vlookup indexof
asked Nov 24 '18 at 20:54
dokidoki
589
asked Nov 24 '18 at 20:54
dokidoki
589
asked Nov 24 '18 at 20:54
dokidoki
589
asked Nov 24 '18 at 20:54
dokidoki
589
asked Nov 24 '18 at 20:54
dokidoki
589
589
1
Index will use the target range ie column, match uses the lookup range - have a look at the vlookup its index column and result column.
– Solar Mike
Nov 24 '18 at 21:32
There are examples of the use of index and match on this site - worth looking for...
– Solar Mike
Nov 25 '18 at 6:41
add a comment |
1
Index will use the target range ie column, match uses the lookup range - have a look at the vlookup its index column and result column.
– Solar Mike
Nov 24 '18 at 21:32
There are examples of the use of index and match on this site - worth looking for...
– Solar Mike
Nov 25 '18 at 6:41
1
1
Index will use the target range ie column, match uses the lookup range - have a look at the vlookup its index column and result column.
– Solar Mike
Nov 24 '18 at 21:32
Index will use the target range ie column, match uses the lookup range - have a look at the vlookup its index column and result column.
– Solar Mike
Nov 24 '18 at 21:32
There are examples of the use of index and match on this site - worth looking for...
– Solar Mike
Nov 25 '18 at 6:41
There are examples of the use of index and match on this site - worth looking for...
– Solar Mike
Nov 25 '18 at 6:41
add a comment |
1 Answer
1
active
oldest
votes
For such queries the best way is to try them yourself, even if you fail at first but you would learn even if you fail or succeed, the best way would have been that you had searched the internet for index match and try to implement that on your data.
but as you are here, I shall try to make it clear how it is done
Match function finds a value in a range and when it finds it; it will return its position in that range (which is the relative row number if we have provided a column range)
Index function can be used on that returned position. We can give index a range and provide a position to it. It return a value at this particular location from the provided range.
The trick is; that in this case we shall provide a different column and it will return data from that column
I am going to explain one row (#8), all other rows are same
- Match Result B8: it is using match() to search the id in A8 in the
range A2 to A4, and when it finds that range it is returning its
position in that range which is 2 - Index result C8: it is using index function() to retrieve the value
at position 2 (returned by match in previous line) from the range D2
to D4, as it is nanana it will return that.
D8 to D10 is same formula but combined
The formulas in the cells are as below
B8: =MATCH(A8,$A$2:$A$4)
C8: =INDEX($D$2:$D$4,B8)
D8: =INDEX($D$2:$D$4,MATCH(A8,$A$2:$A$4))
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
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%2f53462277%2findex-match-instead-using-vlookup%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
For such queries the best way is to try them yourself, even if you fail at first but you would learn even if you fail or succeed, the best way would have been that you had searched the internet for index match and try to implement that on your data.
but as you are here, I shall try to make it clear how it is done
Match function finds a value in a range and when it finds it; it will return its position in that range (which is the relative row number if we have provided a column range)
Index function can be used on that returned position. We can give index a range and provide a position to it. It return a value at this particular location from the provided range.
The trick is; that in this case we shall provide a different column and it will return data from that column
I am going to explain one row (#8), all other rows are same
- Match Result B8: it is using match() to search the id in A8 in the
range A2 to A4, and when it finds that range it is returning its
position in that range which is 2 - Index result C8: it is using index function() to retrieve the value
at position 2 (returned by match in previous line) from the range D2
to D4, as it is nanana it will return that.
D8 to D10 is same formula but combined
The formulas in the cells are as below
B8: =MATCH(A8,$A$2:$A$4)
C8: =INDEX($D$2:$D$4,B8)
D8: =INDEX($D$2:$D$4,MATCH(A8,$A$2:$A$4))
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
add a comment |
For such queries the best way is to try them yourself, even if you fail at first but you would learn even if you fail or succeed, the best way would have been that you had searched the internet for index match and try to implement that on your data.
but as you are here, I shall try to make it clear how it is done
Match function finds a value in a range and when it finds it; it will return its position in that range (which is the relative row number if we have provided a column range)
Index function can be used on that returned position. We can give index a range and provide a position to it. It return a value at this particular location from the provided range.
The trick is; that in this case we shall provide a different column and it will return data from that column
I am going to explain one row (#8), all other rows are same
- Match Result B8: it is using match() to search the id in A8 in the
range A2 to A4, and when it finds that range it is returning its
position in that range which is 2 - Index result C8: it is using index function() to retrieve the value
at position 2 (returned by match in previous line) from the range D2
to D4, as it is nanana it will return that.
D8 to D10 is same formula but combined
The formulas in the cells are as below
B8: =MATCH(A8,$A$2:$A$4)
C8: =INDEX($D$2:$D$4,B8)
D8: =INDEX($D$2:$D$4,MATCH(A8,$A$2:$A$4))
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
add a comment |
For such queries the best way is to try them yourself, even if you fail at first but you would learn even if you fail or succeed, the best way would have been that you had searched the internet for index match and try to implement that on your data.
but as you are here, I shall try to make it clear how it is done
Match function finds a value in a range and when it finds it; it will return its position in that range (which is the relative row number if we have provided a column range)
Index function can be used on that returned position. We can give index a range and provide a position to it. It return a value at this particular location from the provided range.
The trick is; that in this case we shall provide a different column and it will return data from that column
I am going to explain one row (#8), all other rows are same
- Match Result B8: it is using match() to search the id in A8 in the
range A2 to A4, and when it finds that range it is returning its
position in that range which is 2 - Index result C8: it is using index function() to retrieve the value
at position 2 (returned by match in previous line) from the range D2
to D4, as it is nanana it will return that.
D8 to D10 is same formula but combined
The formulas in the cells are as below
B8: =MATCH(A8,$A$2:$A$4)
C8: =INDEX($D$2:$D$4,B8)
D8: =INDEX($D$2:$D$4,MATCH(A8,$A$2:$A$4))
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
For such queries the best way is to try them yourself, even if you fail at first but you would learn even if you fail or succeed, the best way would have been that you had searched the internet for index match and try to implement that on your data.
but as you are here, I shall try to make it clear how it is done
Match function finds a value in a range and when it finds it; it will return its position in that range (which is the relative row number if we have provided a column range)
Index function can be used on that returned position. We can give index a range and provide a position to it. It return a value at this particular location from the provided range.
The trick is; that in this case we shall provide a different column and it will return data from that column
I am going to explain one row (#8), all other rows are same
- Match Result B8: it is using match() to search the id in A8 in the
range A2 to A4, and when it finds that range it is returning its
position in that range which is 2 - Index result C8: it is using index function() to retrieve the value
at position 2 (returned by match in previous line) from the range D2
to D4, as it is nanana it will return that.
D8 to D10 is same formula but combined
The formulas in the cells are as below
B8: =MATCH(A8,$A$2:$A$4)
C8: =INDEX($D$2:$D$4,B8)
D8: =INDEX($D$2:$D$4,MATCH(A8,$A$2:$A$4))
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
answered Nov 25 '18 at 5:42
usmanhaqusmanhaq
1,123128
1,123128
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
add a comment |
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
It may be worth making clear that the range length must be the same for both index and match...
– Solar Mike
Nov 25 '18 at 7:24
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
amazing! thank you so much on this explanation!
– doki
Nov 25 '18 at 11:26
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%2f53462277%2findex-match-instead-using-vlookup%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
Index will use the target range ie column, match uses the lookup range - have a look at the vlookup its index column and result column.
– Solar Mike
Nov 24 '18 at 21:32
There are examples of the use of index and match on this site - worth looking for...
– Solar Mike
Nov 25 '18 at 6:41