How to find first date after given date in a sorted column
I have two columns of sorted dates in Excel. Given a date in column 1, how do I find the first date in column 2 after the given date?
microsoft-excel worksheet-function
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
add a comment |
I have two columns of sorted dates in Excel. Given a date in column 1, how do I find the first date in column 2 after the given date?
microsoft-excel worksheet-function
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
add a comment |
I have two columns of sorted dates in Excel. Given a date in column 1, how do I find the first date in column 2 after the given date?
microsoft-excel worksheet-function
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
I have two columns of sorted dates in Excel. Given a date in column 1, how do I find the first date in column 2 after the given date?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
edited Apr 2 '15 at 18:13
Excellll
11.1k74163
11.1k74163
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
asked Apr 2 '15 at 16:36
Trevor NewhookTrevor Newhook
12417
12417
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.
=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)
The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your dates are sorted, the next date in the list will occur after the date in A2, so adding 1 to this index will give you what you want.
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
@TrevorNewhook Just change the first argument of theINDEXfunction toC2:C30.
– Excellll
Apr 2 '15 at 18:12
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f897164%2fhow-to-find-first-date-after-given-date-in-a-sorted-column%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
You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.
=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)
The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your dates are sorted, the next date in the list will occur after the date in A2, so adding 1 to this index will give you what you want.
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
@TrevorNewhook Just change the first argument of theINDEXfunction toC2:C30.
– Excellll
Apr 2 '15 at 18:12
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
add a comment |
You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.
=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)
The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your dates are sorted, the next date in the list will occur after the date in A2, so adding 1 to this index will give you what you want.
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
@TrevorNewhook Just change the first argument of theINDEXfunction toC2:C30.
– Excellll
Apr 2 '15 at 18:12
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
add a comment |
You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.
=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)
The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your dates are sorted, the next date in the list will occur after the date in A2, so adding 1 to this index will give you what you want.
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.
=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)
The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your dates are sorted, the next date in the list will occur after the date in A2, so adding 1 to this index will give you what you want.
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
answered Apr 2 '15 at 16:53
ExcellllExcellll
11.1k74163
11.1k74163
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
@TrevorNewhook Just change the first argument of theINDEXfunction toC2:C30.
– Excellll
Apr 2 '15 at 18:12
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
add a comment |
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
@TrevorNewhook Just change the first argument of theINDEXfunction toC2:C30.
– Excellll
Apr 2 '15 at 18:12
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
what would I add to that formula to get the value in column C of the row where B matches A2?
– Trevor Newhook
Apr 2 '15 at 17:19
@TrevorNewhook Just change the first argument of the
INDEX function to C2:C30.– Excellll
Apr 2 '15 at 18:12
@TrevorNewhook Just change the first argument of the
INDEX function to C2:C30.– Excellll
Apr 2 '15 at 18:12
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
I figured it out - thanks for the help
– Trevor Newhook
Apr 2 '15 at 20:53
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f897164%2fhow-to-find-first-date-after-given-date-in-a-sorted-column%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
