Mixing
Alternative to INDIRECT() in Excel
I am currently using a formula =MATCH(A2,INDIRECT(Q20):INDIRECT(Q22),0) where A2 contains an item to be searched in a range. The range is dynamic, Q20 holds the start point as =ADDRESS(Q17,3,4) and Q22 holds =ADDRESS(Q18,3,4). Q17 and Q18 contain a formula to calculate the desired row number based on user input in a specific cell B2. As you can see, the range is generated dynamically.
Can anyone suggest doing the same using a non-volatile function ? This is to be used in multiple places and it slows down the entire spreadsheet if done so. I also want to keep Manual Calculation disabled as I need to return updated results as soon as a the input is entered in the specific cell B2.
excel
asked Jan 1 at 11:33
ytoamnytoamn
1178
add a comment |
I am currently using a formula =MATCH(A2,INDIRECT(Q20):INDIRECT(Q22),0) where A2 contains an item to be searched in a range. The range is dynamic, Q20 holds the start point as =ADDRESS(Q17,3,4) and Q22 holds =ADDRESS(Q18,3,4). Q17 and Q18 contain a formula to calculate the desired row number based on user input in a specific cell B2. As you can see, the range is generated dynamically.
Can anyone suggest doing the same using a non-volatile function ? This is to be used in multiple places and it slows down the entire spreadsheet if done so. I also want to keep Manual Calculation disabled as I need to return updated results as soon as a the input is entered in the specific cell B2.
excel
asked Jan 1 at 11:33
ytoamnytoamn
1178
Are the addresses always in column C?
– user10852207
Jan 1 at 11:38
@user10852207, yes always
– ytoamn
Jan 1 at 11:39
add a comment |
I am currently using a formula =MATCH(A2,INDIRECT(Q20):INDIRECT(Q22),0) where A2 contains an item to be searched in a range. The range is dynamic, Q20 holds the start point as =ADDRESS(Q17,3,4) and Q22 holds =ADDRESS(Q18,3,4). Q17 and Q18 contain a formula to calculate the desired row number based on user input in a specific cell B2. As you can see, the range is generated dynamically.
Can anyone suggest doing the same using a non-volatile function ? This is to be used in multiple places and it slows down the entire spreadsheet if done so. I also want to keep Manual Calculation disabled as I need to return updated results as soon as a the input is entered in the specific cell B2.
excel
asked Jan 1 at 11:33
ytoamnytoamn
1178
I am currently using a formula =MATCH(A2,INDIRECT(Q20):INDIRECT(Q22),0) where A2 contains an item to be searched in a range. The range is dynamic, Q20 holds the start point as =ADDRESS(Q17,3,4) and Q22 holds =ADDRESS(Q18,3,4). Q17 and Q18 contain a formula to calculate the desired row number based on user input in a specific cell B2. As you can see, the range is generated dynamically.
Can anyone suggest doing the same using a non-volatile function ? This is to be used in multiple places and it slows down the entire spreadsheet if done so. I also want to keep Manual Calculation disabled as I need to return updated results as soon as a the input is entered in the specific cell B2.
excel
excel
asked Jan 1 at 11:33
ytoamnytoamn
1178
asked Jan 1 at 11:33
ytoamnytoamn
1178
edited Jan 1 at 11:43
ytoamn
asked Jan 1 at 11:33
ytoamnytoamn
1178
asked Jan 1 at 11:33
ytoamnytoamn
1178
asked Jan 1 at 11:33
ytoamnytoamn
1178
1178
Are the addresses always in column C?
– user10852207
Jan 1 at 11:38
@user10852207, yes always
– ytoamn
Jan 1 at 11:39
add a comment |
Are the addresses always in column C?
– user10852207
Jan 1 at 11:38
@user10852207, yes always
– ytoamn
Jan 1 at 11:39
Are the addresses always in column C?
– user10852207
Jan 1 at 11:38
Are the addresses always in column C?
– user10852207
Jan 1 at 11:38
@user10852207, yes always
– ytoamn
Jan 1 at 11:39
@user10852207, yes always
– ytoamn
Jan 1 at 11:39
add a comment |
1 Answer
1
active
oldest
votes
INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.
=MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
'if always in column C then the range can be tightened.
=MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)
answered Jan 1 at 11:42
user10852207user10852207
2263
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53995083%2falternative-to-indirect-in-excel%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
INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.
=MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
'if always in column C then the range can be tightened.
=MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)
answered Jan 1 at 11:42
user10852207user10852207
2263
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
add a comment |
INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.
=MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
'if always in column C then the range can be tightened.
=MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)
answered Jan 1 at 11:42
user10852207user10852207
2263
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
add a comment |
INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.
=MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
'if always in column C then the range can be tightened.
=MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)
answered Jan 1 at 11:42
user10852207user10852207
2263
INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.
=MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
'if always in column C then the range can be tightened.
=MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)
answered Jan 1 at 11:42
user10852207user10852207
2263
answered Jan 1 at 11:42
user10852207user10852207
2263
answered Jan 1 at 11:42
user10852207user10852207
2263
answered Jan 1 at 11:42
user10852207user10852207
2263
2263
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
add a comment |
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
Bingo, Thanks a lot. Both solutions work, however I prefer the second as I dont want to canvas the whole sheet
– ytoamn
Jan 1 at 11:47
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53995083%2falternative-to-indirect-in-excel%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
Are the addresses always in column C?
– user10852207
Jan 1 at 11:38
@user10852207, yes always
– ytoamn
Jan 1 at 11:39