Using VLOOKUP with data in nonadjacent columns
I have my data stored in
and
I want to pull a subset of that data based on the selection in a dropdown. For example, if I select "Org 1" in the dropdown, then all the indicator data (Indicators 1-15 will populate) next to the label for that indicator. I know how to do this when the data is adjacent -- with the names of the orgs in a column that is next to the corresponding data. For this, I have used the following formula:
=VLOOKUP(C1,'Data Lookup 1718'!A3:B16,2,FALSE)
However, this means that in the worksheet with my data I need to replicate the column of orgs 15 times so that the org names are always next to the indicator data. Is there another way to do this--preferably a formula that will allow me to pull data from nonadjacent cells? I've attached my data for reference. Thank you!
microsoft-excel vlookup
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
add a comment |
I have my data stored in
and
I want to pull a subset of that data based on the selection in a dropdown. For example, if I select "Org 1" in the dropdown, then all the indicator data (Indicators 1-15 will populate) next to the label for that indicator. I know how to do this when the data is adjacent -- with the names of the orgs in a column that is next to the corresponding data. For this, I have used the following formula:
=VLOOKUP(C1,'Data Lookup 1718'!A3:B16,2,FALSE)
However, this means that in the worksheet with my data I need to replicate the column of orgs 15 times so that the org names are always next to the indicator data. Is there another way to do this--preferably a formula that will allow me to pull data from nonadjacent cells? I've attached my data for reference. Thank you!
microsoft-excel vlookup
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
Is the dropdown inC1? What about the Indicators, where are those? And where is the non-adjacent data? This question is unclear to anyone who can't see your spreadsheet. Can you please edit your question to provide sample data and expected result?
– cybernetic.nomad
Dec 16 '18 at 20:15
Sorry for that -- I was not able to attach the Excel sheet but have attached some screenshots. Yes, the dropdown is in C1. Hopefully the screenshots make it more clear but please let me know if not and I can add more details. Thank you so much for your help!
– Shamiso
Dec 17 '18 at 7:30
add a comment |
I have my data stored in
and
I want to pull a subset of that data based on the selection in a dropdown. For example, if I select "Org 1" in the dropdown, then all the indicator data (Indicators 1-15 will populate) next to the label for that indicator. I know how to do this when the data is adjacent -- with the names of the orgs in a column that is next to the corresponding data. For this, I have used the following formula:
=VLOOKUP(C1,'Data Lookup 1718'!A3:B16,2,FALSE)
However, this means that in the worksheet with my data I need to replicate the column of orgs 15 times so that the org names are always next to the indicator data. Is there another way to do this--preferably a formula that will allow me to pull data from nonadjacent cells? I've attached my data for reference. Thank you!
microsoft-excel vlookup
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
I have my data stored in
and
I want to pull a subset of that data based on the selection in a dropdown. For example, if I select "Org 1" in the dropdown, then all the indicator data (Indicators 1-15 will populate) next to the label for that indicator. I know how to do this when the data is adjacent -- with the names of the orgs in a column that is next to the corresponding data. For this, I have used the following formula:
=VLOOKUP(C1,'Data Lookup 1718'!A3:B16,2,FALSE)
However, this means that in the worksheet with my data I need to replicate the column of orgs 15 times so that the org names are always next to the indicator data. Is there another way to do this--preferably a formula that will allow me to pull data from nonadjacent cells? I've attached my data for reference. Thank you!
microsoft-excel vlookup
microsoft-excel vlookup
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
edited Dec 17 '18 at 13:12
cybernetic.nomad
1,307112
1,307112
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
asked Dec 16 '18 at 15:35
ShamisoShamiso
53
53
Is the dropdown inC1? What about the Indicators, where are those? And where is the non-adjacent data? This question is unclear to anyone who can't see your spreadsheet. Can you please edit your question to provide sample data and expected result?
– cybernetic.nomad
Dec 16 '18 at 20:15
Sorry for that -- I was not able to attach the Excel sheet but have attached some screenshots. Yes, the dropdown is in C1. Hopefully the screenshots make it more clear but please let me know if not and I can add more details. Thank you so much for your help!
– Shamiso
Dec 17 '18 at 7:30
add a comment |
Is the dropdown inC1? What about the Indicators, where are those? And where is the non-adjacent data? This question is unclear to anyone who can't see your spreadsheet. Can you please edit your question to provide sample data and expected result?
– cybernetic.nomad
Dec 16 '18 at 20:15
Sorry for that -- I was not able to attach the Excel sheet but have attached some screenshots. Yes, the dropdown is in C1. Hopefully the screenshots make it more clear but please let me know if not and I can add more details. Thank you so much for your help!
– Shamiso
Dec 17 '18 at 7:30
Is the dropdown in
C1? What about the Indicators, where are those? And where is the non-adjacent data? This question is unclear to anyone who can't see your spreadsheet. Can you please edit your question to provide sample data and expected result?– cybernetic.nomad
Dec 16 '18 at 20:15
Is the dropdown in
C1? What about the Indicators, where are those? And where is the non-adjacent data? This question is unclear to anyone who can't see your spreadsheet. Can you please edit your question to provide sample data and expected result?– cybernetic.nomad
Dec 16 '18 at 20:15
Sorry for that -- I was not able to attach the Excel sheet but have attached some screenshots. Yes, the dropdown is in C1. Hopefully the screenshots make it more clear but please let me know if not and I can add more details. Thank you so much for your help!
– Shamiso
Dec 17 '18 at 7:30
Sorry for that -- I was not able to attach the Excel sheet but have attached some screenshots. Yes, the dropdown is in C1. Hopefully the screenshots make it more clear but please let me know if not and I can add more details. Thank you so much for your help!
– Shamiso
Dec 17 '18 at 7:30
add a comment |
1 Answer
1
active
oldest
votes
Try:
=VLOOKUP(C$1,'Data Lookup 1718'!A$3:P$16,ROW()-1,FALSE)
This is the same VLOOKUP you are using with two changes:
1) Use the entire data table (I assumed it ends at column P since there are 15 indicators.
2) Use the row the formula is in (ROW()) as the basis for the index of the column to use in the VLOOKUP
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
=ROW()returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in theData Lookup 1718worksheet
– cybernetic.nomad
Dec 17 '18 at 19:36
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%2f1385028%2fusing-vlookup-with-data-in-nonadjacent-columns%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
Try:
=VLOOKUP(C$1,'Data Lookup 1718'!A$3:P$16,ROW()-1,FALSE)
This is the same VLOOKUP you are using with two changes:
1) Use the entire data table (I assumed it ends at column P since there are 15 indicators.
2) Use the row the formula is in (ROW()) as the basis for the index of the column to use in the VLOOKUP
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
=ROW()returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in theData Lookup 1718worksheet
– cybernetic.nomad
Dec 17 '18 at 19:36
add a comment |
Try:
=VLOOKUP(C$1,'Data Lookup 1718'!A$3:P$16,ROW()-1,FALSE)
This is the same VLOOKUP you are using with two changes:
1) Use the entire data table (I assumed it ends at column P since there are 15 indicators.
2) Use the row the formula is in (ROW()) as the basis for the index of the column to use in the VLOOKUP
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
=ROW()returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in theData Lookup 1718worksheet
– cybernetic.nomad
Dec 17 '18 at 19:36
add a comment |
Try:
=VLOOKUP(C$1,'Data Lookup 1718'!A$3:P$16,ROW()-1,FALSE)
This is the same VLOOKUP you are using with two changes:
1) Use the entire data table (I assumed it ends at column P since there are 15 indicators.
2) Use the row the formula is in (ROW()) as the basis for the index of the column to use in the VLOOKUP
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
Try:
=VLOOKUP(C$1,'Data Lookup 1718'!A$3:P$16,ROW()-1,FALSE)
This is the same VLOOKUP you are using with two changes:
1) Use the entire data table (I assumed it ends at column P since there are 15 indicators.
2) Use the row the formula is in (ROW()) as the basis for the index of the column to use in the VLOOKUP
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
answered Dec 17 '18 at 12:53
cybernetic.nomadcybernetic.nomad
1,307112
1,307112
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
=ROW()returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in theData Lookup 1718worksheet
– cybernetic.nomad
Dec 17 '18 at 19:36
add a comment |
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
=ROW()returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in theData Lookup 1718worksheet
– cybernetic.nomad
Dec 17 '18 at 19:36
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
Thanks so much for your response. I'm not sure I understand the 2nd item. The formulas are in the H column. What row should I be referencing in the formula?
– Shamiso
Dec 17 '18 at 19:07
=ROW() returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in the Data Lookup 1718 worksheet– cybernetic.nomad
Dec 17 '18 at 19:36
=ROW() returns the row it is typed in (try it!). The formula is using the fact that it is in a different row for each indicator to access the different columns in the Data Lookup 1718 worksheet– cybernetic.nomad
Dec 17 '18 at 19:36
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%2f1385028%2fusing-vlookup-with-data-in-nonadjacent-columns%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
Is the dropdown in
C1? What about the Indicators, where are those? And where is the non-adjacent data? This question is unclear to anyone who can't see your spreadsheet. Can you please edit your question to provide sample data and expected result?– cybernetic.nomad
Dec 16 '18 at 20:15
Sorry for that -- I was not able to attach the Excel sheet but have attached some screenshots. Yes, the dropdown is in C1. Hopefully the screenshots make it more clear but please let me know if not and I can add more details. Thank you so much for your help!
– Shamiso
Dec 17 '18 at 7:30