Excel formula to get first non-empty value and then return value other row
This formula:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0))
looks up the first non-zero value from column Q till the end in row 10.
And it displays the value present in that cell. Suppose the cell found is V10.
I must display the value in cell V7.
I cannot change the range to lookup the first non-empty cell, because the higher rows are always filled.
There probably are two possible answers
One answer that looks up 3 rows higher (10-7=3) and another answer that looks up in the fixed row V$7.
I need the last option.
microsoft-excel worksheet-function
asked Aug 17 '17 at 16:00
GerardGerard
48111
add a comment |
This formula:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0))
looks up the first non-zero value from column Q till the end in row 10.
And it displays the value present in that cell. Suppose the cell found is V10.
I must display the value in cell V7.
I cannot change the range to lookup the first non-empty cell, because the higher rows are always filled.
There probably are two possible answers
One answer that looks up 3 rows higher (10-7=3) and another answer that looks up in the fixed row V$7.
I need the last option.
microsoft-excel worksheet-function
asked Aug 17 '17 at 16:00
GerardGerard
48111
I think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH(().
– Bandersnatch
Aug 17 '17 at 16:23
Or you could just do this:=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3)
– Bandersnatch
Aug 17 '17 at 16:32
OFFSETworks, other suggestion fails. Using 3 -ROW()I get an offset that copies to other rows. All right, thanks.
– Gerard
Aug 17 '17 at 18:53
add a comment |
This formula:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0))
looks up the first non-zero value from column Q till the end in row 10.
And it displays the value present in that cell. Suppose the cell found is V10.
I must display the value in cell V7.
I cannot change the range to lookup the first non-empty cell, because the higher rows are always filled.
There probably are two possible answers
One answer that looks up 3 rows higher (10-7=3) and another answer that looks up in the fixed row V$7.
I need the last option.
microsoft-excel worksheet-function
asked Aug 17 '17 at 16:00
GerardGerard
48111
This formula:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0))
looks up the first non-zero value from column Q till the end in row 10.
And it displays the value present in that cell. Suppose the cell found is V10.
I must display the value in cell V7.
I cannot change the range to lookup the first non-empty cell, because the higher rows are always filled.
There probably are two possible answers
One answer that looks up 3 rows higher (10-7=3) and another answer that looks up in the fixed row V$7.
I need the last option.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Aug 17 '17 at 16:00
GerardGerard
48111
asked Aug 17 '17 at 16:00
GerardGerard
48111
edited Aug 17 '17 at 17:40
Gerard
asked Aug 17 '17 at 16:00
GerardGerard
48111
asked Aug 17 '17 at 16:00
GerardGerard
48111
asked Aug 17 '17 at 16:00
GerardGerard
48111
48111
I think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH(().
– Bandersnatch
Aug 17 '17 at 16:23
Or you could just do this:=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3)
– Bandersnatch
Aug 17 '17 at 16:32
OFFSETworks, other suggestion fails. Using 3 -ROW()I get an offset that copies to other rows. All right, thanks.
– Gerard
Aug 17 '17 at 18:53
add a comment |
I think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH(().
– Bandersnatch
Aug 17 '17 at 16:23
Or you could just do this:=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3)
– Bandersnatch
Aug 17 '17 at 16:32
OFFSETworks, other suggestion fails. Using 3 -ROW()I get an offset that copies to other rows. All right, thanks.
– Gerard
Aug 17 '17 at 18:53
I think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH(().
– Bandersnatch
Aug 17 '17 at 16:23
I think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH(().
– Bandersnatch
Aug 17 '17 at 16:23
Or you could just do this:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3)– Bandersnatch
Aug 17 '17 at 16:32
Or you could just do this:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3)– Bandersnatch
Aug 17 '17 at 16:32
OFFSET works, other suggestion fails. Using 3 - ROW() I get an offset that copies to other rows. All right, thanks.– Gerard
Aug 17 '17 at 18:53
OFFSET works, other suggestion fails. Using 3 - ROW() I get an offset that copies to other rows. All right, thanks.– Gerard
Aug 17 '17 at 18:53
add a comment |
1 Answer
1
active
oldest
votes
Suppose the cell found is V10.
I must display the value in cell V7.
Then I'll propose a simple " if (row 10 is not zero ) & if( sum(from the left) = 0 ) " test will do, (assuming cell P10 is empty).
in Q7, put =IF(AND(Q10<>0,SUM($P$10:P10)=0),Q10,"")
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
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%2f1241888%2fexcel-formula-to-get-first-non-empty-value-and-then-return-value-other-row%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
Suppose the cell found is V10.
I must display the value in cell V7.
Then I'll propose a simple " if (row 10 is not zero ) & if( sum(from the left) = 0 ) " test will do, (assuming cell P10 is empty).
in Q7, put =IF(AND(Q10<>0,SUM($P$10:P10)=0),Q10,"")
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
add a comment |
Suppose the cell found is V10.
I must display the value in cell V7.
Then I'll propose a simple " if (row 10 is not zero ) & if( sum(from the left) = 0 ) " test will do, (assuming cell P10 is empty).
in Q7, put =IF(AND(Q10<>0,SUM($P$10:P10)=0),Q10,"")
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
add a comment |
Suppose the cell found is V10.
I must display the value in cell V7.
Then I'll propose a simple " if (row 10 is not zero ) & if( sum(from the left) = 0 ) " test will do, (assuming cell P10 is empty).
in Q7, put =IF(AND(Q10<>0,SUM($P$10:P10)=0),Q10,"")
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
Suppose the cell found is V10.
I must display the value in cell V7.
Then I'll propose a simple " if (row 10 is not zero ) & if( sum(from the left) = 0 ) " test will do, (assuming cell P10 is empty).
in Q7, put =IF(AND(Q10<>0,SUM($P$10:P10)=0),Q10,"")
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
answered Jan 8 at 7:13
p._phidot_p._phidot_
57429
57429
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%2f1241888%2fexcel-formula-to-get-first-non-empty-value-and-then-return-value-other-row%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 think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH(().
– Bandersnatch
Aug 17 '17 at 16:23
Or you could just do this:
=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3)– Bandersnatch
Aug 17 '17 at 16:32
OFFSETworks, other suggestion fails. Using 3 -ROW()I get an offset that copies to other rows. All right, thanks.– Gerard
Aug 17 '17 at 18:53