Index Match Large multiple criteria
I need some help please.
=INDEX(Data!$L:$L,MATCH(1,INDEX((Data!$O:$O=LARGE(Data!$O:$O,ROWS(Calcs!F$1:F3)))*(COUNTIF(Calcs!F$1:F3,Data!$O:$O)=0),),0))
I have this formula to look through data and pull the top 5 site numbers (SIN) starting with the largest amount of hours.
What I need the formula to do is to search by branch number so that when I select a branch (branch number is updated in cell A1 of "Calcs Tab") it will look in the data table and pull the Site number (SIN) for the largest 5 sites (most hours) by branch (that's in cell A1).
My data tab is below:
This formula is in cell F2 on the "Calcs Tab" and I drag it down to get the 2nd, 3rd, 4th etc (Highest Hours).
As requested, this is the data:
Please help, sorry if this is not very clear. My first ever post!
Thank You
microsoft-excel
asked Feb 27 at 18:19
SineadSinead
62
add a comment |
I need some help please.
=INDEX(Data!$L:$L,MATCH(1,INDEX((Data!$O:$O=LARGE(Data!$O:$O,ROWS(Calcs!F$1:F3)))*(COUNTIF(Calcs!F$1:F3,Data!$O:$O)=0),),0))
I have this formula to look through data and pull the top 5 site numbers (SIN) starting with the largest amount of hours.
What I need the formula to do is to search by branch number so that when I select a branch (branch number is updated in cell A1 of "Calcs Tab") it will look in the data table and pull the Site number (SIN) for the largest 5 sites (most hours) by branch (that's in cell A1).
My data tab is below:
This formula is in cell F2 on the "Calcs Tab" and I drag it down to get the 2nd, 3rd, 4th etc (Highest Hours).
As requested, this is the data:
Please help, sorry if this is not very clear. My first ever post!
Thank You
microsoft-excel
asked Feb 27 at 18:19
SineadSinead
62
Better edit your post and attach the real Screen Shot so that we may understand the Data structure properly to fix the issue!!
– Rajesh S
Feb 28 at 7:10
@Sinead It appears that you may have accidentally created two accounts (here and here) which may be why you couldn't edit your question. You may wish to have a look at the Help Center article I accidentally created two accounts; how do I merge them? for instructions on fixing this issue.
– Anaksunaman
Feb 28 at 11:19
Thank you @Anaksunaman. I have now merged my accounts
– Sinead
Feb 28 at 11:29
@RajeshS I have updated it, hope it is more clearer.
– Sinead
Feb 28 at 11:42
add a comment |
I need some help please.
=INDEX(Data!$L:$L,MATCH(1,INDEX((Data!$O:$O=LARGE(Data!$O:$O,ROWS(Calcs!F$1:F3)))*(COUNTIF(Calcs!F$1:F3,Data!$O:$O)=0),),0))
I have this formula to look through data and pull the top 5 site numbers (SIN) starting with the largest amount of hours.
What I need the formula to do is to search by branch number so that when I select a branch (branch number is updated in cell A1 of "Calcs Tab") it will look in the data table and pull the Site number (SIN) for the largest 5 sites (most hours) by branch (that's in cell A1).
My data tab is below:
This formula is in cell F2 on the "Calcs Tab" and I drag it down to get the 2nd, 3rd, 4th etc (Highest Hours).
As requested, this is the data:
Please help, sorry if this is not very clear. My first ever post!
Thank You
microsoft-excel
asked Feb 27 at 18:19
SineadSinead
62
I need some help please.
=INDEX(Data!$L:$L,MATCH(1,INDEX((Data!$O:$O=LARGE(Data!$O:$O,ROWS(Calcs!F$1:F3)))*(COUNTIF(Calcs!F$1:F3,Data!$O:$O)=0),),0))
I have this formula to look through data and pull the top 5 site numbers (SIN) starting with the largest amount of hours.
What I need the formula to do is to search by branch number so that when I select a branch (branch number is updated in cell A1 of "Calcs Tab") it will look in the data table and pull the Site number (SIN) for the largest 5 sites (most hours) by branch (that's in cell A1).
My data tab is below:
This formula is in cell F2 on the "Calcs Tab" and I drag it down to get the 2nd, 3rd, 4th etc (Highest Hours).
As requested, this is the data:
Please help, sorry if this is not very clear. My first ever post!
Thank You
microsoft-excel
microsoft-excel
asked Feb 27 at 18:19
SineadSinead
62
asked Feb 27 at 18:19
SineadSinead
62
edited Feb 28 at 13:11
Sinead
asked Feb 27 at 18:19
SineadSinead
62
asked Feb 27 at 18:19
SineadSinead
62
asked Feb 27 at 18:19
SineadSinead
62
62
Better edit your post and attach the real Screen Shot so that we may understand the Data structure properly to fix the issue!!
– Rajesh S
Feb 28 at 7:10
@Sinead It appears that you may have accidentally created two accounts (here and here) which may be why you couldn't edit your question. You may wish to have a look at the Help Center article I accidentally created two accounts; how do I merge them? for instructions on fixing this issue.
– Anaksunaman
Feb 28 at 11:19
Thank you @Anaksunaman. I have now merged my accounts
– Sinead
Feb 28 at 11:29
@RajeshS I have updated it, hope it is more clearer.
– Sinead
Feb 28 at 11:42
add a comment |
Better edit your post and attach the real Screen Shot so that we may understand the Data structure properly to fix the issue!!
– Rajesh S
Feb 28 at 7:10
@Sinead It appears that you may have accidentally created two accounts (here and here) which may be why you couldn't edit your question. You may wish to have a look at the Help Center article I accidentally created two accounts; how do I merge them? for instructions on fixing this issue.
– Anaksunaman
Feb 28 at 11:19
Thank you @Anaksunaman. I have now merged my accounts
– Sinead
Feb 28 at 11:29
@RajeshS I have updated it, hope it is more clearer.
– Sinead
Feb 28 at 11:42
Better edit your post and attach the real Screen Shot so that we may understand the Data structure properly to fix the issue!!
– Rajesh S
Feb 28 at 7:10
Better edit your post and attach the real Screen Shot so that we may understand the Data structure properly to fix the issue!!
– Rajesh S
Feb 28 at 7:10
@Sinead It appears that you may have accidentally created two accounts (here and here) which may be why you couldn't edit your question. You may wish to have a look at the Help Center article I accidentally created two accounts; how do I merge them? for instructions on fixing this issue.
– Anaksunaman
Feb 28 at 11:19
@Sinead It appears that you may have accidentally created two accounts (here and here) which may be why you couldn't edit your question. You may wish to have a look at the Help Center article I accidentally created two accounts; how do I merge them? for instructions on fixing this issue.
– Anaksunaman
Feb 28 at 11:19
Thank you @Anaksunaman. I have now merged my accounts
– Sinead
Feb 28 at 11:29
Thank you @Anaksunaman. I have now merged my accounts
– Sinead
Feb 28 at 11:29
@RajeshS I have updated it, hope it is more clearer.
– Sinead
Feb 28 at 11:42
@RajeshS I have updated it, hope it is more clearer.
– Sinead
Feb 28 at 11:42
add a comment |
1 Answer
1
active
oldest
votes
Data:
Result:
How it works:
- Fill
1to5from C22 to C26
to set Order to getLargest Hoursin
adjacent Column. - Fill Branch No in B22.
Formula in A22:
=IF(B22=6,"Bristol",IF(B22=10,"Glassgow",IF(B22=2,"London",IF(B22=9,"Liverpool",IF(B22=8,"Barmingham","")))))
Array (CSE) Formula in D22, finish with Ctrl+Shift+Enter and fill down.
{=SUMPRODUCT(LARGE(($A$2:$A$19=$B$22)*($B$2:$B$19=$A$22)*($E$2:$E$19),C22))}
Enter this Array (CSE) Formula in E22, finish with Ctrl+Shift+Enter & fill down:
{=IFERROR(INDEX(C$2:C$19,MATCH(1,($A$22=$B$2:$B$19)*($B$22=$A$2:$A$19)*(D22=$E$2:$E$19),0)),"")}
N.B.
As soon you change
Branch Noin
B22, you getSINin E22
downwards.Adjust cell references in the Formula as
needed.For easy identification I've applied
Redcolors to
& GreenBranch No 6and
10.
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
add a comment |
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%2f1409947%2findex-match-large-multiple-criteria%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
Data:
Result:
How it works:
- Fill
1to5from C22 to C26
to set Order to getLargest Hoursin
adjacent Column. - Fill Branch No in B22.
Formula in A22:
=IF(B22=6,"Bristol",IF(B22=10,"Glassgow",IF(B22=2,"London",IF(B22=9,"Liverpool",IF(B22=8,"Barmingham","")))))
Array (CSE) Formula in D22, finish with Ctrl+Shift+Enter and fill down.
{=SUMPRODUCT(LARGE(($A$2:$A$19=$B$22)*($B$2:$B$19=$A$22)*($E$2:$E$19),C22))}
Enter this Array (CSE) Formula in E22, finish with Ctrl+Shift+Enter & fill down:
{=IFERROR(INDEX(C$2:C$19,MATCH(1,($A$22=$B$2:$B$19)*($B$22=$A$2:$A$19)*(D22=$E$2:$E$19),0)),"")}
N.B.
As soon you change
Branch Noin
B22, you getSINin E22
downwards.Adjust cell references in the Formula as
needed.For easy identification I've applied
Redcolors to
& GreenBranch No 6and
10.
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
add a comment |
Data:
Result:
How it works:
- Fill
1to5from C22 to C26
to set Order to getLargest Hoursin
adjacent Column. - Fill Branch No in B22.
Formula in A22:
=IF(B22=6,"Bristol",IF(B22=10,"Glassgow",IF(B22=2,"London",IF(B22=9,"Liverpool",IF(B22=8,"Barmingham","")))))
Array (CSE) Formula in D22, finish with Ctrl+Shift+Enter and fill down.
{=SUMPRODUCT(LARGE(($A$2:$A$19=$B$22)*($B$2:$B$19=$A$22)*($E$2:$E$19),C22))}
Enter this Array (CSE) Formula in E22, finish with Ctrl+Shift+Enter & fill down:
{=IFERROR(INDEX(C$2:C$19,MATCH(1,($A$22=$B$2:$B$19)*($B$22=$A$2:$A$19)*(D22=$E$2:$E$19),0)),"")}
N.B.
As soon you change
Branch Noin
B22, you getSINin E22
downwards.Adjust cell references in the Formula as
needed.For easy identification I've applied
Redcolors to
& GreenBranch No 6and
10.
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
add a comment |
Data:
Result:
How it works:
- Fill
1to5from C22 to C26
to set Order to getLargest Hoursin
adjacent Column. - Fill Branch No in B22.
Formula in A22:
=IF(B22=6,"Bristol",IF(B22=10,"Glassgow",IF(B22=2,"London",IF(B22=9,"Liverpool",IF(B22=8,"Barmingham","")))))
Array (CSE) Formula in D22, finish with Ctrl+Shift+Enter and fill down.
{=SUMPRODUCT(LARGE(($A$2:$A$19=$B$22)*($B$2:$B$19=$A$22)*($E$2:$E$19),C22))}
Enter this Array (CSE) Formula in E22, finish with Ctrl+Shift+Enter & fill down:
{=IFERROR(INDEX(C$2:C$19,MATCH(1,($A$22=$B$2:$B$19)*($B$22=$A$2:$A$19)*(D22=$E$2:$E$19),0)),"")}
N.B.
As soon you change
Branch Noin
B22, you getSINin E22
downwards.Adjust cell references in the Formula as
needed.For easy identification I've applied
Redcolors to
& GreenBranch No 6and
10.
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
Data:
Result:
How it works:
- Fill
1to5from C22 to C26
to set Order to getLargest Hoursin
adjacent Column. - Fill Branch No in B22.
Formula in A22:
=IF(B22=6,"Bristol",IF(B22=10,"Glassgow",IF(B22=2,"London",IF(B22=9,"Liverpool",IF(B22=8,"Barmingham","")))))
Array (CSE) Formula in D22, finish with Ctrl+Shift+Enter and fill down.
{=SUMPRODUCT(LARGE(($A$2:$A$19=$B$22)*($B$2:$B$19=$A$22)*($E$2:$E$19),C22))}
Enter this Array (CSE) Formula in E22, finish with Ctrl+Shift+Enter & fill down:
{=IFERROR(INDEX(C$2:C$19,MATCH(1,($A$22=$B$2:$B$19)*($B$22=$A$2:$A$19)*(D22=$E$2:$E$19),0)),"")}
N.B.
As soon you change
Branch Noin
B22, you getSINin E22
downwards.Adjust cell references in the Formula as
needed.For easy identification I've applied
Redcolors to
& GreenBranch No 6and
10.
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
edited Mar 4 at 9:05
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
answered Mar 1 at 9:15
Rajesh SRajesh S
4,3652624
4,3652624
add a comment |
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%2f1409947%2findex-match-large-multiple-criteria%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
Better edit your post and attach the real Screen Shot so that we may understand the Data structure properly to fix the issue!!
– Rajesh S
Feb 28 at 7:10
@Sinead It appears that you may have accidentally created two accounts (here and here) which may be why you couldn't edit your question. You may wish to have a look at the Help Center article I accidentally created two accounts; how do I merge them? for instructions on fixing this issue.
– Anaksunaman
Feb 28 at 11:19
Thank you @Anaksunaman. I have now merged my accounts
– Sinead
Feb 28 at 11:29
@RajeshS I have updated it, hope it is more clearer.
– Sinead
Feb 28 at 11:42