Excel - INDEX MATCH MATCH with a twist
I'm trying to assist some local firefighters make a crewing display page on their excel roster. Their roster is setup with dates from 1st January -> 31st December vertically in column A with the names of each firefighter across the top in Row 1. They then fill out which role any given person is working within the matrix.
On the display page they will have a section for each fire truck and want to display who is the driver for Truck 1, who is the pump operator for Truck 2 etc etc.
Now I'm familiar with using INDEX MATCH MATCH for finding a row and column and then return the intersecting value. However in this case I need to find the date (vertically) then find the role on the same row as the found date (e.g. "Truck 1 Driver") and then return the column header (Name of firefighter performing that role on that day)
I'm very capable with VBA but I would like to avoid using it if I can achieve the same result with a forumla. Unfortunately my formulas skills are lacking as I usually just use VBA.
microsoft-excel
edited Jan 17 at 19:36
Dave M
12.7k92838
asked Jan 17 at 19:18
iShaymusiShaymus
1032
add a comment |
I'm trying to assist some local firefighters make a crewing display page on their excel roster. Their roster is setup with dates from 1st January -> 31st December vertically in column A with the names of each firefighter across the top in Row 1. They then fill out which role any given person is working within the matrix.
On the display page they will have a section for each fire truck and want to display who is the driver for Truck 1, who is the pump operator for Truck 2 etc etc.
Now I'm familiar with using INDEX MATCH MATCH for finding a row and column and then return the intersecting value. However in this case I need to find the date (vertically) then find the role on the same row as the found date (e.g. "Truck 1 Driver") and then return the column header (Name of firefighter performing that role on that day)
I'm very capable with VBA but I would like to avoid using it if I can achieve the same result with a forumla. Unfortunately my formulas skills are lacking as I usually just use VBA.
microsoft-excel
edited Jan 17 at 19:36
Dave M
12.7k92838
asked Jan 17 at 19:18
iShaymusiShaymus
1032
add a comment |
I'm trying to assist some local firefighters make a crewing display page on their excel roster. Their roster is setup with dates from 1st January -> 31st December vertically in column A with the names of each firefighter across the top in Row 1. They then fill out which role any given person is working within the matrix.
On the display page they will have a section for each fire truck and want to display who is the driver for Truck 1, who is the pump operator for Truck 2 etc etc.
Now I'm familiar with using INDEX MATCH MATCH for finding a row and column and then return the intersecting value. However in this case I need to find the date (vertically) then find the role on the same row as the found date (e.g. "Truck 1 Driver") and then return the column header (Name of firefighter performing that role on that day)
I'm very capable with VBA but I would like to avoid using it if I can achieve the same result with a forumla. Unfortunately my formulas skills are lacking as I usually just use VBA.
microsoft-excel
edited Jan 17 at 19:36
Dave M
12.7k92838
asked Jan 17 at 19:18
iShaymusiShaymus
1032
I'm trying to assist some local firefighters make a crewing display page on their excel roster. Their roster is setup with dates from 1st January -> 31st December vertically in column A with the names of each firefighter across the top in Row 1. They then fill out which role any given person is working within the matrix.
On the display page they will have a section for each fire truck and want to display who is the driver for Truck 1, who is the pump operator for Truck 2 etc etc.
Now I'm familiar with using INDEX MATCH MATCH for finding a row and column and then return the intersecting value. However in this case I need to find the date (vertically) then find the role on the same row as the found date (e.g. "Truck 1 Driver") and then return the column header (Name of firefighter performing that role on that day)
I'm very capable with VBA but I would like to avoid using it if I can achieve the same result with a forumla. Unfortunately my formulas skills are lacking as I usually just use VBA.
microsoft-excel
microsoft-excel
edited Jan 17 at 19:36
Dave M
12.7k92838
asked Jan 17 at 19:18
iShaymusiShaymus
1032
edited Jan 17 at 19:36
Dave M
12.7k92838
asked Jan 17 at 19:18
iShaymusiShaymus
1032
edited Jan 17 at 19:36
Dave M
12.7k92838
edited Jan 17 at 19:36
Dave M
12.7k92838
edited Jan 17 at 19:36
Dave M
12.7k92838
12.7k92838
asked Jan 17 at 19:18
iShaymusiShaymus
1032
asked Jan 17 at 19:18
iShaymusiShaymus
1032
asked Jan 17 at 19:18
iShaymusiShaymus
1032
1032
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Use an INDEX/MATCH to return the correct row to the MATCH which will return the correct column to another INDEX:
=INDEX($B$1:$D$1,MATCH(G$1,INDEX($B:$D,MATCH($F2,$A:$A,0),0),0))
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
SoG$1being the date lookup and$F2being the shift name lookup?
– iShaymus
Jan 17 at 19:37
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
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%2f1395506%2fexcel-index-match-match-with-a-twist%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
Use an INDEX/MATCH to return the correct row to the MATCH which will return the correct column to another INDEX:
=INDEX($B$1:$D$1,MATCH(G$1,INDEX($B:$D,MATCH($F2,$A:$A,0),0),0))
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
SoG$1being the date lookup and$F2being the shift name lookup?
– iShaymus
Jan 17 at 19:37
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
add a comment |
Use an INDEX/MATCH to return the correct row to the MATCH which will return the correct column to another INDEX:
=INDEX($B$1:$D$1,MATCH(G$1,INDEX($B:$D,MATCH($F2,$A:$A,0),0),0))
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
SoG$1being the date lookup and$F2being the shift name lookup?
– iShaymus
Jan 17 at 19:37
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
add a comment |
Use an INDEX/MATCH to return the correct row to the MATCH which will return the correct column to another INDEX:
=INDEX($B$1:$D$1,MATCH(G$1,INDEX($B:$D,MATCH($F2,$A:$A,0),0),0))
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
Use an INDEX/MATCH to return the correct row to the MATCH which will return the correct column to another INDEX:
=INDEX($B$1:$D$1,MATCH(G$1,INDEX($B:$D,MATCH($F2,$A:$A,0),0),0))
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
answered Jan 17 at 19:26
Scott CranerScott Craner
11.8k1815
11.8k1815
SoG$1being the date lookup and$F2being the shift name lookup?
– iShaymus
Jan 17 at 19:37
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
add a comment |
SoG$1being the date lookup and$F2being the shift name lookup?
– iShaymus
Jan 17 at 19:37
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
So
G$1 being the date lookup and $F2 being the shift name lookup?– iShaymus
Jan 17 at 19:37
So
G$1 being the date lookup and $F2 being the shift name lookup?– iShaymus
Jan 17 at 19:37
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
no the other way around. see the picture. F2 is the date and G1 is the shift name lookup.
– Scott Craner
Jan 17 at 19:39
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Sorry I'm on a rather locked down IT system currently and cant view imgr links
– iShaymus
Jan 17 at 19:40
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
Works like a charm. I had to create a few named ranges due to bouncing between two sheets, the formula was getting a bit long for excel's liking. Thanks!
– iShaymus
Jan 17 at 20:03
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%2f1395506%2fexcel-index-match-match-with-a-twist%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
