Using INDEX/MATCH to return a value from a set of tables
I'm having a hard time writing an INDEX/MATCH formula. I need the formula to look through multiple rows to find the one that matches my lookup value. I have multiple headers for different months.
The current formula I have in cell B2 is:
=INDEX($D5:$M5,MATCH($B$1,$D$4:$M$4,0))
As you can see, if I type January in B1, B2 displays a. If I type FebruaryI would like it to display 99.
microsoft-excel worksheet-function
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
add a comment |
I'm having a hard time writing an INDEX/MATCH formula. I need the formula to look through multiple rows to find the one that matches my lookup value. I have multiple headers for different months.
The current formula I have in cell B2 is:
=INDEX($D5:$M5,MATCH($B$1,$D$4:$M$4,0))
As you can see, if I type January in B1, B2 displays a. If I type FebruaryI would like it to display 99.
microsoft-excel worksheet-function
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
1
Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned?
– patkim
Dec 27 '18 at 5:13
Are there more conditionals?
– Lee
Dec 27 '18 at 7:26
1
You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements.
– fixer1234
Dec 28 '18 at 2:51
add a comment |
I'm having a hard time writing an INDEX/MATCH formula. I need the formula to look through multiple rows to find the one that matches my lookup value. I have multiple headers for different months.
The current formula I have in cell B2 is:
=INDEX($D5:$M5,MATCH($B$1,$D$4:$M$4,0))
As you can see, if I type January in B1, B2 displays a. If I type FebruaryI would like it to display 99.
microsoft-excel worksheet-function
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
I'm having a hard time writing an INDEX/MATCH formula. I need the formula to look through multiple rows to find the one that matches my lookup value. I have multiple headers for different months.
The current formula I have in cell B2 is:
=INDEX($D5:$M5,MATCH($B$1,$D$4:$M$4,0))
As you can see, if I type January in B1, B2 displays a. If I type FebruaryI would like it to display 99.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
edited Dec 30 '18 at 14:36
robinCTS
4,01741527
4,01741527
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
asked Dec 27 '18 at 1:36
Jose CortezJose Cortez
35
35
1
Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned?
– patkim
Dec 27 '18 at 5:13
Are there more conditionals?
– Lee
Dec 27 '18 at 7:26
1
You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements.
– fixer1234
Dec 28 '18 at 2:51
add a comment |
1
Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned?
– patkim
Dec 27 '18 at 5:13
Are there more conditionals?
– Lee
Dec 27 '18 at 7:26
1
You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements.
– fixer1234
Dec 28 '18 at 2:51
1
1
Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned?
– patkim
Dec 27 '18 at 5:13
Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned?
– patkim
Dec 27 '18 at 5:13
Are there more conditionals?
– Lee
Dec 27 '18 at 7:26
Are there more conditionals?
– Lee
Dec 27 '18 at 7:26
1
1
You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements.
– fixer1234
Dec 28 '18 at 2:51
You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements.
– fixer1234
Dec 28 '18 at 2:51
add a comment |
2 Answers
2
active
oldest
votes
Looking at your previous questions, it looks like you are trying to extract the data for a specific column given two lookup criteria - the Month and the Branch.
For simplicity's sake, I'm also assuming that the Branches are exactly the same for each sub-table, i.e they all exist and are in the same order.
With your worksheet modified as follows,
enter the following formula in B3:
=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)
Explanation:
Instead of indexing into a row, the formula indexes into the required data column, i.e. D4:D16.
The first MATCH() finds the matching Month row, whilst the second one finds the offset to the matching Branch. The -1 is required to convert the one-based index returned by the second MATCH() to a zero-based offset. (And yes,MATCH($B$2,$C$4:$C$16,0)-1 could have been replaced by MATCH($B$2,$C$5:$C$16,0), but it is easier to understand with the -1.)
Notes:
- The way the formula has been written means that filling the formula to the right extracts the next column's value. That's why the
D4:D16is a relative reference. - Alternatively, to obtain another column's data, just replace the
D4:D16with the appropriate reference. (The absolute reference,$D$4:$D$16, needs to remain unchanged if a single Month header is used, as explained next.) - Multiple headers for each month are not required, even when extracting data from a column other than the first one. Only the first data column needs one or, alternatively, a merged header can be used instead. (This is why the
$D$4:$D$16is an absolute reference.)
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
add a comment |
My answer have two options:
Using Data Set shown in Screen Shot.
Solution with modified Data Set.
Option 1:
I would like to suggest a method, which comprises a simple trick to manage different data sets according to the Month.
How it works:
- You need to create Helper Cell in
B41, which reflects Month's Name in CellB42. - Formula in Cell
B42:
=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))
- Formula in Cell
B43, drag it Down.
=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))
Note:
- Second Formula need to be extended as soon rest of month's data included.
Option 2:
Enter this Formula in Cell
B51& fill it Down
=INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))
Adjust cell references in the Formula as needed.
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
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%2f1387989%2fusing-index-match-to-return-a-value-from-a-set-of-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Looking at your previous questions, it looks like you are trying to extract the data for a specific column given two lookup criteria - the Month and the Branch.
For simplicity's sake, I'm also assuming that the Branches are exactly the same for each sub-table, i.e they all exist and are in the same order.
With your worksheet modified as follows,
enter the following formula in B3:
=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)
Explanation:
Instead of indexing into a row, the formula indexes into the required data column, i.e. D4:D16.
The first MATCH() finds the matching Month row, whilst the second one finds the offset to the matching Branch. The -1 is required to convert the one-based index returned by the second MATCH() to a zero-based offset. (And yes,MATCH($B$2,$C$4:$C$16,0)-1 could have been replaced by MATCH($B$2,$C$5:$C$16,0), but it is easier to understand with the -1.)
Notes:
- The way the formula has been written means that filling the formula to the right extracts the next column's value. That's why the
D4:D16is a relative reference. - Alternatively, to obtain another column's data, just replace the
D4:D16with the appropriate reference. (The absolute reference,$D$4:$D$16, needs to remain unchanged if a single Month header is used, as explained next.) - Multiple headers for each month are not required, even when extracting data from a column other than the first one. Only the first data column needs one or, alternatively, a merged header can be used instead. (This is why the
$D$4:$D$16is an absolute reference.)
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
add a comment |
Looking at your previous questions, it looks like you are trying to extract the data for a specific column given two lookup criteria - the Month and the Branch.
For simplicity's sake, I'm also assuming that the Branches are exactly the same for each sub-table, i.e they all exist and are in the same order.
With your worksheet modified as follows,
enter the following formula in B3:
=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)
Explanation:
Instead of indexing into a row, the formula indexes into the required data column, i.e. D4:D16.
The first MATCH() finds the matching Month row, whilst the second one finds the offset to the matching Branch. The -1 is required to convert the one-based index returned by the second MATCH() to a zero-based offset. (And yes,MATCH($B$2,$C$4:$C$16,0)-1 could have been replaced by MATCH($B$2,$C$5:$C$16,0), but it is easier to understand with the -1.)
Notes:
- The way the formula has been written means that filling the formula to the right extracts the next column's value. That's why the
D4:D16is a relative reference. - Alternatively, to obtain another column's data, just replace the
D4:D16with the appropriate reference. (The absolute reference,$D$4:$D$16, needs to remain unchanged if a single Month header is used, as explained next.) - Multiple headers for each month are not required, even when extracting data from a column other than the first one. Only the first data column needs one or, alternatively, a merged header can be used instead. (This is why the
$D$4:$D$16is an absolute reference.)
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
add a comment |
Looking at your previous questions, it looks like you are trying to extract the data for a specific column given two lookup criteria - the Month and the Branch.
For simplicity's sake, I'm also assuming that the Branches are exactly the same for each sub-table, i.e they all exist and are in the same order.
With your worksheet modified as follows,
enter the following formula in B3:
=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)
Explanation:
Instead of indexing into a row, the formula indexes into the required data column, i.e. D4:D16.
The first MATCH() finds the matching Month row, whilst the second one finds the offset to the matching Branch. The -1 is required to convert the one-based index returned by the second MATCH() to a zero-based offset. (And yes,MATCH($B$2,$C$4:$C$16,0)-1 could have been replaced by MATCH($B$2,$C$5:$C$16,0), but it is easier to understand with the -1.)
Notes:
- The way the formula has been written means that filling the formula to the right extracts the next column's value. That's why the
D4:D16is a relative reference. - Alternatively, to obtain another column's data, just replace the
D4:D16with the appropriate reference. (The absolute reference,$D$4:$D$16, needs to remain unchanged if a single Month header is used, as explained next.) - Multiple headers for each month are not required, even when extracting data from a column other than the first one. Only the first data column needs one or, alternatively, a merged header can be used instead. (This is why the
$D$4:$D$16is an absolute reference.)
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
Looking at your previous questions, it looks like you are trying to extract the data for a specific column given two lookup criteria - the Month and the Branch.
For simplicity's sake, I'm also assuming that the Branches are exactly the same for each sub-table, i.e they all exist and are in the same order.
With your worksheet modified as follows,
enter the following formula in B3:
=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)
Explanation:
Instead of indexing into a row, the formula indexes into the required data column, i.e. D4:D16.
The first MATCH() finds the matching Month row, whilst the second one finds the offset to the matching Branch. The -1 is required to convert the one-based index returned by the second MATCH() to a zero-based offset. (And yes,MATCH($B$2,$C$4:$C$16,0)-1 could have been replaced by MATCH($B$2,$C$5:$C$16,0), but it is easier to understand with the -1.)
Notes:
- The way the formula has been written means that filling the formula to the right extracts the next column's value. That's why the
D4:D16is a relative reference. - Alternatively, to obtain another column's data, just replace the
D4:D16with the appropriate reference. (The absolute reference,$D$4:$D$16, needs to remain unchanged if a single Month header is used, as explained next.) - Multiple headers for each month are not required, even when extracting data from a column other than the first one. Only the first data column needs one or, alternatively, a merged header can be used instead. (This is why the
$D$4:$D$16is an absolute reference.)
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
answered Dec 30 '18 at 14:36
robinCTSrobinCTS
4,01741527
4,01741527
add a comment |
add a comment |
My answer have two options:
Using Data Set shown in Screen Shot.
Solution with modified Data Set.
Option 1:
I would like to suggest a method, which comprises a simple trick to manage different data sets according to the Month.
How it works:
- You need to create Helper Cell in
B41, which reflects Month's Name in CellB42. - Formula in Cell
B42:
=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))
- Formula in Cell
B43, drag it Down.
=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))
Note:
- Second Formula need to be extended as soon rest of month's data included.
Option 2:
Enter this Formula in Cell
B51& fill it Down
=INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))
Adjust cell references in the Formula as needed.
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
add a comment |
My answer have two options:
Using Data Set shown in Screen Shot.
Solution with modified Data Set.
Option 1:
I would like to suggest a method, which comprises a simple trick to manage different data sets according to the Month.
How it works:
- You need to create Helper Cell in
B41, which reflects Month's Name in CellB42. - Formula in Cell
B42:
=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))
- Formula in Cell
B43, drag it Down.
=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))
Note:
- Second Formula need to be extended as soon rest of month's data included.
Option 2:
Enter this Formula in Cell
B51& fill it Down
=INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))
Adjust cell references in the Formula as needed.
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
add a comment |
My answer have two options:
Using Data Set shown in Screen Shot.
Solution with modified Data Set.
Option 1:
I would like to suggest a method, which comprises a simple trick to manage different data sets according to the Month.
How it works:
- You need to create Helper Cell in
B41, which reflects Month's Name in CellB42. - Formula in Cell
B42:
=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))
- Formula in Cell
B43, drag it Down.
=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))
Note:
- Second Formula need to be extended as soon rest of month's data included.
Option 2:
Enter this Formula in Cell
B51& fill it Down
=INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))
Adjust cell references in the Formula as needed.
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
My answer have two options:
Using Data Set shown in Screen Shot.
Solution with modified Data Set.
Option 1:
I would like to suggest a method, which comprises a simple trick to manage different data sets according to the Month.
How it works:
- You need to create Helper Cell in
B41, which reflects Month's Name in CellB42. - Formula in Cell
B42:
=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))
- Formula in Cell
B43, drag it Down.
=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))
Note:
- Second Formula need to be extended as soon rest of month's data included.
Option 2:
Enter this Formula in Cell
B51& fill it Down
=INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))
Adjust cell references in the Formula as needed.
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
answered Dec 27 '18 at 8:35
Rajesh SRajesh S
1
1
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
add a comment |
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
Just write your concern,, Y down voted ??
– Rajesh S
Dec 28 '18 at 7:26
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%2f1387989%2fusing-index-match-to-return-a-value-from-a-set-of-tables%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
Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned?
– patkim
Dec 27 '18 at 5:13
Are there more conditionals?
– Lee
Dec 27 '18 at 7:26
1
You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements.
– fixer1234
Dec 28 '18 at 2:51