Index Match multiple criteria and multiple rows google sheets
up vote
0
down vote
favorite
I am trying to match date and time, and metric category with multiple rows in a data sheet. Currently, I record metrics every day in a similar function with a set of rows showing metric data based on intervals of time. These rows are broken up by date.
I would like to be able to add to a dashboard the current metrics without someone needing to dig through the tables I am recording in. This would require the metric to stay up there until the next interval changed.
I will use =SPLIT(NOW) for the date and time but for now I would like to at least get this to work with static interval and date. I tried using Index Match using AND(), & etc and I cannot get it to work. I also tried to use an array but it errors every time.
Google Sheets Index Match Multi Criteria
arrays indexing google-sheets excel-formula match
asked Nov 11 at 19:57
jlandis
1
add a comment |
up vote
0
down vote
favorite
I am trying to match date and time, and metric category with multiple rows in a data sheet. Currently, I record metrics every day in a similar function with a set of rows showing metric data based on intervals of time. These rows are broken up by date.
I would like to be able to add to a dashboard the current metrics without someone needing to dig through the tables I am recording in. This would require the metric to stay up there until the next interval changed.
I will use =SPLIT(NOW) for the date and time but for now I would like to at least get this to work with static interval and date. I tried using Index Match using AND(), & etc and I cannot get it to work. I also tried to use an array but it errors every time.
Google Sheets Index Match Multi Criteria
arrays indexing google-sheets excel-formula match
asked Nov 11 at 19:57
jlandis
1
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am trying to match date and time, and metric category with multiple rows in a data sheet. Currently, I record metrics every day in a similar function with a set of rows showing metric data based on intervals of time. These rows are broken up by date.
I would like to be able to add to a dashboard the current metrics without someone needing to dig through the tables I am recording in. This would require the metric to stay up there until the next interval changed.
I will use =SPLIT(NOW) for the date and time but for now I would like to at least get this to work with static interval and date. I tried using Index Match using AND(), & etc and I cannot get it to work. I also tried to use an array but it errors every time.
Google Sheets Index Match Multi Criteria
arrays indexing google-sheets excel-formula match
asked Nov 11 at 19:57
jlandis
1
I am trying to match date and time, and metric category with multiple rows in a data sheet. Currently, I record metrics every day in a similar function with a set of rows showing metric data based on intervals of time. These rows are broken up by date.
I would like to be able to add to a dashboard the current metrics without someone needing to dig through the tables I am recording in. This would require the metric to stay up there until the next interval changed.
I will use =SPLIT(NOW) for the date and time but for now I would like to at least get this to work with static interval and date. I tried using Index Match using AND(), & etc and I cannot get it to work. I also tried to use an array but it errors every time.
Google Sheets Index Match Multi Criteria
arrays indexing google-sheets excel-formula match
arrays indexing google-sheets excel-formula match
asked Nov 11 at 19:57
jlandis
1
asked Nov 11 at 19:57
jlandis
1
asked Nov 11 at 19:57
jlandis
1
asked Nov 11 at 19:57
jlandis
1
asked Nov 11 at 19:57
jlandis
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
If the time intervals for each day will be exactly the same and the data structure will never change with 3 identical lines per day (as per your screenshot) than I think you are overcomplicating things a little:
B2:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+1,MATCH(B$1,$A$7:$E$7,0))
B3:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
B4:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
answered Nov 11 at 22:08
Michal Rosa
1,239614
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',
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%2f53252646%2findex-match-multiple-criteria-and-multiple-rows-google-sheets%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
up vote
0
down vote
If the time intervals for each day will be exactly the same and the data structure will never change with 3 identical lines per day (as per your screenshot) than I think you are overcomplicating things a little:
B2:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+1,MATCH(B$1,$A$7:$E$7,0))
B3:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
B4:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
answered Nov 11 at 22:08
Michal Rosa
1,239614
add a comment |
up vote
0
down vote
If the time intervals for each day will be exactly the same and the data structure will never change with 3 identical lines per day (as per your screenshot) than I think you are overcomplicating things a little:
B2:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+1,MATCH(B$1,$A$7:$E$7,0))
B3:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
B4:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
answered Nov 11 at 22:08
Michal Rosa
1,239614
add a comment |
up vote
0
down vote
up vote
0
down vote
If the time intervals for each day will be exactly the same and the data structure will never change with 3 identical lines per day (as per your screenshot) than I think you are overcomplicating things a little:
B2:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+1,MATCH(B$1,$A$7:$E$7,0))
B3:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
B4:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
answered Nov 11 at 22:08
Michal Rosa
1,239614
If the time intervals for each day will be exactly the same and the data structure will never change with 3 identical lines per day (as per your screenshot) than I think you are overcomplicating things a little:
B2:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+1,MATCH(B$1,$A$7:$E$7,0))
B3:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
B4:
=index($A$7:$E$15,MATCH($A$1,$A$7:$A$15,0)+2,MATCH(B$1,$A$7:$E$7,0))
answered Nov 11 at 22:08
Michal Rosa
1,239614
answered Nov 11 at 22:08
Michal Rosa
1,239614
answered Nov 11 at 22:08
Michal Rosa
1,239614
answered Nov 11 at 22:08
Michal Rosa
1,239614
1,239614
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53252646%2findex-match-multiple-criteria-and-multiple-rows-google-sheets%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
