How to use Excel Offset function where the reference cell is a formula output?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a formula =OFFSET($AL$5,12,0) that gives me the value I want from column AL. I want to make the "$AL$5" part dynamic, and looked up to match the same value in column F.
=(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5"
But when I try to combine the two formulas, Excel errors out:
=OFFSET(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0))),12,0)*100
Does anyone know how to make this work? Thanks in advance!
microsoft-excel worksheet-function
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
add a comment |
I have a formula =OFFSET($AL$5,12,0) that gives me the value I want from column AL. I want to make the "$AL$5" part dynamic, and looked up to match the same value in column F.
=(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5"
But when I try to combine the two formulas, Excel errors out:
=OFFSET(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0))),12,0)*100
Does anyone know how to make this work? Thanks in advance!
microsoft-excel worksheet-function
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
I don’t understand what you’re trying to do. It feels like you’re using an approach that unnecessarily complicated. Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Feb 8 at 0:36
add a comment |
I have a formula =OFFSET($AL$5,12,0) that gives me the value I want from column AL. I want to make the "$AL$5" part dynamic, and looked up to match the same value in column F.
=(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5"
But when I try to combine the two formulas, Excel errors out:
=OFFSET(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0))),12,0)*100
Does anyone know how to make this work? Thanks in advance!
microsoft-excel worksheet-function
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
I have a formula =OFFSET($AL$5,12,0) that gives me the value I want from column AL. I want to make the "$AL$5" part dynamic, and looked up to match the same value in column F.
=(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5"
But when I try to combine the two formulas, Excel errors out:
=OFFSET(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0))),12,0)*100
Does anyone know how to make this work? Thanks in advance!
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
asked Feb 8 at 0:26
Randy RandalmanRandy Randalman
1
1
I don’t understand what you’re trying to do. It feels like you’re using an approach that unnecessarily complicated. Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Feb 8 at 0:36
add a comment |
I don’t understand what you’re trying to do. It feels like you’re using an approach that unnecessarily complicated. Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Feb 8 at 0:36
I don’t understand what you’re trying to do. It feels like you’re using an approach that unnecessarily complicated. Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Feb 8 at 0:36
I don’t understand what you’re trying to do. It feels like you’re using an approach that unnecessarily complicated. Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Feb 8 at 0:36
add a comment |
1 Answer
1
active
oldest
votes
If this =(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5", then the output is still text, not really a cell address. In order to convert it to a cell address, you need to wrap an Indirect() around it.
But since you're using Index/Match already, you don't need to convert the cell address into a text and then convert it back to a cell address.
Index returns a range and you can use that inside the offset.
Or, better yet: since you seem to offset 12 rows from the cell found by Index, you might as well build that into the Index statement directly. Add 12 rows to the number found by Match.
The final formula is
=INDEX(AL:AL,MATCH(F2,$AL:$AL,0)+12)*100
In words: In column AL, look for the value specified in F2, go down 12 rows and multiply that value by 100.
Let me know if that works for you.
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
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%2f1403355%2fhow-to-use-excel-offset-function-where-the-reference-cell-is-a-formula-output%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
If this =(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5", then the output is still text, not really a cell address. In order to convert it to a cell address, you need to wrap an Indirect() around it.
But since you're using Index/Match already, you don't need to convert the cell address into a text and then convert it back to a cell address.
Index returns a range and you can use that inside the offset.
Or, better yet: since you seem to offset 12 rows from the cell found by Index, you might as well build that into the Index statement directly. Add 12 rows to the number found by Match.
The final formula is
=INDEX(AL:AL,MATCH(F2,$AL:$AL,0)+12)*100
In words: In column AL, look for the value specified in F2, go down 12 rows and multiply that value by 100.
Let me know if that works for you.
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
add a comment |
If this =(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5", then the output is still text, not really a cell address. In order to convert it to a cell address, you need to wrap an Indirect() around it.
But since you're using Index/Match already, you don't need to convert the cell address into a text and then convert it back to a cell address.
Index returns a range and you can use that inside the offset.
Or, better yet: since you seem to offset 12 rows from the cell found by Index, you might as well build that into the Index statement directly. Add 12 rows to the number found by Match.
The final formula is
=INDEX(AL:AL,MATCH(F2,$AL:$AL,0)+12)*100
In words: In column AL, look for the value specified in F2, go down 12 rows and multiply that value by 100.
Let me know if that works for you.
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
add a comment |
If this =(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5", then the output is still text, not really a cell address. In order to convert it to a cell address, you need to wrap an Indirect() around it.
But since you're using Index/Match already, you don't need to convert the cell address into a text and then convert it back to a cell address.
Index returns a range and you can use that inside the offset.
Or, better yet: since you seem to offset 12 rows from the cell found by Index, you might as well build that into the Index statement directly. Add 12 rows to the number found by Match.
The final formula is
=INDEX(AL:AL,MATCH(F2,$AL:$AL,0)+12)*100
In words: In column AL, look for the value specified in F2, go down 12 rows and multiply that value by 100.
Let me know if that works for you.
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
If this =(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5", then the output is still text, not really a cell address. In order to convert it to a cell address, you need to wrap an Indirect() around it.
But since you're using Index/Match already, you don't need to convert the cell address into a text and then convert it back to a cell address.
Index returns a range and you can use that inside the offset.
Or, better yet: since you seem to offset 12 rows from the cell found by Index, you might as well build that into the Index statement directly. Add 12 rows to the number found by Match.
The final formula is
=INDEX(AL:AL,MATCH(F2,$AL:$AL,0)+12)*100
In words: In column AL, look for the value specified in F2, go down 12 rows and multiply that value by 100.
Let me know if that works for you.
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
edited Feb 9 at 3:17
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
answered Feb 8 at 1:02
teylynteylyn
17.6k22539
17.6k22539
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%2f1403355%2fhow-to-use-excel-offset-function-where-the-reference-cell-is-a-formula-output%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
I don’t understand what you’re trying to do. It feels like you’re using an approach that unnecessarily complicated. Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Feb 8 at 0:36