lookup with named range on separate sheet
I'm building a spreadsheet that has multiple named ranges on a separate sheet and I want to do a lookup to find the value based on the named range so I don't have to manually enter the cell references.
The current formula I have is:
=VLOOKUP(D9,INDIRECT("'MACRS Tables'!"&Arkansas!C9),MATCH(K8,INDIRECT("'MACRS Tables'!"&Arkansas!C9),0),0)
The problem lies in the indirect formula in the match portion of the formula.
It is looking in the entire range instead of looking in just the top row.
How do I fix that?
microsoft-excel worksheet-function
edited Jan 4 at 1:44
Rey Juna
610111
asked Jan 3 at 23:12
CaseyCasey
1
|
show 1 more comment
I'm building a spreadsheet that has multiple named ranges on a separate sheet and I want to do a lookup to find the value based on the named range so I don't have to manually enter the cell references.
The current formula I have is:
=VLOOKUP(D9,INDIRECT("'MACRS Tables'!"&Arkansas!C9),MATCH(K8,INDIRECT("'MACRS Tables'!"&Arkansas!C9),0),0)
The problem lies in the indirect formula in the match portion of the formula.
It is looking in the entire range instead of looking in just the top row.
How do I fix that?
microsoft-excel worksheet-function
edited Jan 4 at 1:44
Rey Juna
610111
asked Jan 3 at 23:12
CaseyCasey
1
What is the value of the cellArkansas!C9? What range does Indirect resolve to?
– teylyn
Jan 4 at 0:59
The value in Arkansas!C9 is _MA200 which refers to the named range (cells A4:V10 on a separate tab) that the lookup should be using on the other tab. The indirect portion pulls from the correct named range, it's just the match portion of the formula that isn't working because it is looking at the entire named range (A4:V10) instead of just the top row (A4:V10). Hopefully that makes sense!
– Casey
Jan 4 at 14:03
Well, if the range name _MA200 refers to the rangeA4:V10, why do you expect that referring that range name would NOT return the whole range? How do you expect it to refer to only the first row? You yourself get it wrong in your previous comment when you sayjust the top row (A4:V10)where it should beA4:V4. So, if you want to refer to just the first row of that range, you can't use the range name for the whole range. Excel cannot read your mind. The first match should look at the first column of the range, the second match at the first row. You always feed it the whole table.
– teylyn
Jan 4 at 20:39
You are misunderstanding what I said. I KNOW that the named range refers to A4:V10. I want help coming up with a formula that will pull the top row of that particular named range without me having to physically go in and select those rows for the formula, otherwise each time I change which range I need to pull the data from, I would have to go in and manually change that formula each time. This would defeat the purpose of what I'm trying to accomplish entirely if I had to do that.
– Casey
Jan 4 at 21:33
I have figured out the problem and got my formula to work. Changed the named range to a table and added "&[#headers]" to the formula.
– Casey
Jan 4 at 23:09
|
show 1 more comment
I'm building a spreadsheet that has multiple named ranges on a separate sheet and I want to do a lookup to find the value based on the named range so I don't have to manually enter the cell references.
The current formula I have is:
=VLOOKUP(D9,INDIRECT("'MACRS Tables'!"&Arkansas!C9),MATCH(K8,INDIRECT("'MACRS Tables'!"&Arkansas!C9),0),0)
The problem lies in the indirect formula in the match portion of the formula.
It is looking in the entire range instead of looking in just the top row.
How do I fix that?
microsoft-excel worksheet-function
edited Jan 4 at 1:44
Rey Juna
610111
asked Jan 3 at 23:12
CaseyCasey
1
I'm building a spreadsheet that has multiple named ranges on a separate sheet and I want to do a lookup to find the value based on the named range so I don't have to manually enter the cell references.
The current formula I have is:
=VLOOKUP(D9,INDIRECT("'MACRS Tables'!"&Arkansas!C9),MATCH(K8,INDIRECT("'MACRS Tables'!"&Arkansas!C9),0),0)
The problem lies in the indirect formula in the match portion of the formula.
It is looking in the entire range instead of looking in just the top row.
How do I fix that?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Jan 4 at 1:44
Rey Juna
610111
asked Jan 3 at 23:12
CaseyCasey
1
edited Jan 4 at 1:44
Rey Juna
610111
asked Jan 3 at 23:12
CaseyCasey
1
edited Jan 4 at 1:44
Rey Juna
610111
edited Jan 4 at 1:44
Rey Juna
610111
edited Jan 4 at 1:44
Rey Juna
610111
610111
asked Jan 3 at 23:12
CaseyCasey
1
asked Jan 3 at 23:12
CaseyCasey
1
asked Jan 3 at 23:12
CaseyCasey
1
1
What is the value of the cellArkansas!C9? What range does Indirect resolve to?
– teylyn
Jan 4 at 0:59
The value in Arkansas!C9 is _MA200 which refers to the named range (cells A4:V10 on a separate tab) that the lookup should be using on the other tab. The indirect portion pulls from the correct named range, it's just the match portion of the formula that isn't working because it is looking at the entire named range (A4:V10) instead of just the top row (A4:V10). Hopefully that makes sense!
– Casey
Jan 4 at 14:03
Well, if the range name _MA200 refers to the rangeA4:V10, why do you expect that referring that range name would NOT return the whole range? How do you expect it to refer to only the first row? You yourself get it wrong in your previous comment when you sayjust the top row (A4:V10)where it should beA4:V4. So, if you want to refer to just the first row of that range, you can't use the range name for the whole range. Excel cannot read your mind. The first match should look at the first column of the range, the second match at the first row. You always feed it the whole table.
– teylyn
Jan 4 at 20:39
You are misunderstanding what I said. I KNOW that the named range refers to A4:V10. I want help coming up with a formula that will pull the top row of that particular named range without me having to physically go in and select those rows for the formula, otherwise each time I change which range I need to pull the data from, I would have to go in and manually change that formula each time. This would defeat the purpose of what I'm trying to accomplish entirely if I had to do that.
– Casey
Jan 4 at 21:33
I have figured out the problem and got my formula to work. Changed the named range to a table and added "&[#headers]" to the formula.
– Casey
Jan 4 at 23:09
|
show 1 more comment
What is the value of the cellArkansas!C9? What range does Indirect resolve to?
– teylyn
Jan 4 at 0:59
The value in Arkansas!C9 is _MA200 which refers to the named range (cells A4:V10 on a separate tab) that the lookup should be using on the other tab. The indirect portion pulls from the correct named range, it's just the match portion of the formula that isn't working because it is looking at the entire named range (A4:V10) instead of just the top row (A4:V10). Hopefully that makes sense!
– Casey
Jan 4 at 14:03
Well, if the range name _MA200 refers to the rangeA4:V10, why do you expect that referring that range name would NOT return the whole range? How do you expect it to refer to only the first row? You yourself get it wrong in your previous comment when you sayjust the top row (A4:V10)where it should beA4:V4. So, if you want to refer to just the first row of that range, you can't use the range name for the whole range. Excel cannot read your mind. The first match should look at the first column of the range, the second match at the first row. You always feed it the whole table.
– teylyn
Jan 4 at 20:39
You are misunderstanding what I said. I KNOW that the named range refers to A4:V10. I want help coming up with a formula that will pull the top row of that particular named range without me having to physically go in and select those rows for the formula, otherwise each time I change which range I need to pull the data from, I would have to go in and manually change that formula each time. This would defeat the purpose of what I'm trying to accomplish entirely if I had to do that.
– Casey
Jan 4 at 21:33
I have figured out the problem and got my formula to work. Changed the named range to a table and added "&[#headers]" to the formula.
– Casey
Jan 4 at 23:09
What is the value of the cell
Arkansas!C9? What range does Indirect resolve to?– teylyn
Jan 4 at 0:59
What is the value of the cell
Arkansas!C9? What range does Indirect resolve to?– teylyn
Jan 4 at 0:59
The value in Arkansas!C9 is _MA200 which refers to the named range (cells A4:V10 on a separate tab) that the lookup should be using on the other tab. The indirect portion pulls from the correct named range, it's just the match portion of the formula that isn't working because it is looking at the entire named range (A4:V10) instead of just the top row (A4:V10). Hopefully that makes sense!
– Casey
Jan 4 at 14:03
The value in Arkansas!C9 is _MA200 which refers to the named range (cells A4:V10 on a separate tab) that the lookup should be using on the other tab. The indirect portion pulls from the correct named range, it's just the match portion of the formula that isn't working because it is looking at the entire named range (A4:V10) instead of just the top row (A4:V10). Hopefully that makes sense!
– Casey
Jan 4 at 14:03
Well, if the range name _MA200 refers to the range
A4:V10, why do you expect that referring that range name would NOT return the whole range? How do you expect it to refer to only the first row? You yourself get it wrong in your previous comment when you say just the top row (A4:V10) where it should be A4:V4. So, if you want to refer to just the first row of that range, you can't use the range name for the whole range. Excel cannot read your mind. The first match should look at the first column of the range, the second match at the first row. You always feed it the whole table.– teylyn
Jan 4 at 20:39
Well, if the range name _MA200 refers to the range
A4:V10, why do you expect that referring that range name would NOT return the whole range? How do you expect it to refer to only the first row? You yourself get it wrong in your previous comment when you say just the top row (A4:V10) where it should be A4:V4. So, if you want to refer to just the first row of that range, you can't use the range name for the whole range. Excel cannot read your mind. The first match should look at the first column of the range, the second match at the first row. You always feed it the whole table.– teylyn
Jan 4 at 20:39
You are misunderstanding what I said. I KNOW that the named range refers to A4:V10. I want help coming up with a formula that will pull the top row of that particular named range without me having to physically go in and select those rows for the formula, otherwise each time I change which range I need to pull the data from, I would have to go in and manually change that formula each time. This would defeat the purpose of what I'm trying to accomplish entirely if I had to do that.
– Casey
Jan 4 at 21:33
You are misunderstanding what I said. I KNOW that the named range refers to A4:V10. I want help coming up with a formula that will pull the top row of that particular named range without me having to physically go in and select those rows for the formula, otherwise each time I change which range I need to pull the data from, I would have to go in and manually change that formula each time. This would defeat the purpose of what I'm trying to accomplish entirely if I had to do that.
– Casey
Jan 4 at 21:33
I have figured out the problem and got my formula to work. Changed the named range to a table and added "&[#headers]" to the formula.
– Casey
Jan 4 at 23:09
I have figured out the problem and got my formula to work. Changed the named range to a table and added "&[#headers]" to the formula.
– Casey
Jan 4 at 23:09
|
show 1 more comment
1 Answer
1
active
oldest
votes
Not sure if this is what you want, but the cross-section of a named range and a row can be referred to as
index([name],[row number in the range])
or as
[name] 4:4
(where 4 is an absolute reference. Not the 4th in the range)
I think if you replace the [name] with the INDIRECT result, you can just use standard VLOOKUP, without the MATCH.
answered Jan 4 at 11:06
JoostJoost
6027
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%2f1390328%2flookup-with-named-range-on-separate-sheet%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
Not sure if this is what you want, but the cross-section of a named range and a row can be referred to as
index([name],[row number in the range])
or as
[name] 4:4
(where 4 is an absolute reference. Not the 4th in the range)
I think if you replace the [name] with the INDIRECT result, you can just use standard VLOOKUP, without the MATCH.
answered Jan 4 at 11:06
JoostJoost
6027
add a comment |
Not sure if this is what you want, but the cross-section of a named range and a row can be referred to as
index([name],[row number in the range])
or as
[name] 4:4
(where 4 is an absolute reference. Not the 4th in the range)
I think if you replace the [name] with the INDIRECT result, you can just use standard VLOOKUP, without the MATCH.
answered Jan 4 at 11:06
JoostJoost
6027
add a comment |
Not sure if this is what you want, but the cross-section of a named range and a row can be referred to as
index([name],[row number in the range])
or as
[name] 4:4
(where 4 is an absolute reference. Not the 4th in the range)
I think if you replace the [name] with the INDIRECT result, you can just use standard VLOOKUP, without the MATCH.
answered Jan 4 at 11:06
JoostJoost
6027
Not sure if this is what you want, but the cross-section of a named range and a row can be referred to as
index([name],[row number in the range])
or as
[name] 4:4
(where 4 is an absolute reference. Not the 4th in the range)
I think if you replace the [name] with the INDIRECT result, you can just use standard VLOOKUP, without the MATCH.
answered Jan 4 at 11:06
JoostJoost
6027
edited Jan 4 at 11:15
answered Jan 4 at 11:06
JoostJoost
6027
answered Jan 4 at 11:06
JoostJoost
6027
answered Jan 4 at 11:06
JoostJoost
6027
6027
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%2f1390328%2flookup-with-named-range-on-separate-sheet%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
What is the value of the cell
Arkansas!C9? What range does Indirect resolve to?– teylyn
Jan 4 at 0:59
The value in Arkansas!C9 is _MA200 which refers to the named range (cells A4:V10 on a separate tab) that the lookup should be using on the other tab. The indirect portion pulls from the correct named range, it's just the match portion of the formula that isn't working because it is looking at the entire named range (A4:V10) instead of just the top row (A4:V10). Hopefully that makes sense!
– Casey
Jan 4 at 14:03
Well, if the range name _MA200 refers to the range
A4:V10, why do you expect that referring that range name would NOT return the whole range? How do you expect it to refer to only the first row? You yourself get it wrong in your previous comment when you sayjust the top row (A4:V10)where it should beA4:V4. So, if you want to refer to just the first row of that range, you can't use the range name for the whole range. Excel cannot read your mind. The first match should look at the first column of the range, the second match at the first row. You always feed it the whole table.– teylyn
Jan 4 at 20:39
You are misunderstanding what I said. I KNOW that the named range refers to A4:V10. I want help coming up with a formula that will pull the top row of that particular named range without me having to physically go in and select those rows for the formula, otherwise each time I change which range I need to pull the data from, I would have to go in and manually change that formula each time. This would defeat the purpose of what I'm trying to accomplish entirely if I had to do that.
– Casey
Jan 4 at 21:33
I have figured out the problem and got my formula to work. Changed the named range to a table and added "&[#headers]" to the formula.
– Casey
Jan 4 at 23:09