Formula to have a result in a column if the entered value is in between the two column list
I am trying to put a formula to figure out a particular cell in a column if the desired value is between two columns. Like if i put a value of 120, the result should show "8", as it is in range of 111 & 124.
microsoft-excel
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
add a comment |
I am trying to put a formula to figure out a particular cell in a column if the desired value is between two columns. Like if i put a value of 120, the result should show "8", as it is in range of 111 & 124.
microsoft-excel
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
Are the values in the first 2 columns non overlapping always?
– patkim
Dec 30 '18 at 13:10
add a comment |
I am trying to put a formula to figure out a particular cell in a column if the desired value is between two columns. Like if i put a value of 120, the result should show "8", as it is in range of 111 & 124.
microsoft-excel
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
I am trying to put a formula to figure out a particular cell in a column if the desired value is between two columns. Like if i put a value of 120, the result should show "8", as it is in range of 111 & 124.
microsoft-excel
microsoft-excel
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
edited Dec 30 '18 at 14:24
Mark Fitzgerald
3681211
3681211
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
asked Dec 30 '18 at 11:50
Jatin GargJatin Garg
61
61
Are the values in the first 2 columns non overlapping always?
– patkim
Dec 30 '18 at 13:10
add a comment |
Are the values in the first 2 columns non overlapping always?
– patkim
Dec 30 '18 at 13:10
Are the values in the first 2 columns non overlapping always?
– patkim
Dec 30 '18 at 13:10
Are the values in the first 2 columns non overlapping always?
– patkim
Dec 30 '18 at 13:10
add a comment |
2 Answers
2
active
oldest
votes
As the two value lists you are comparing against are contiguous (i.e. the second row in column A is one more than the first row column B) you could use an MATCH function on column A without looking at either of the other columns.
=MATCH(120,$A$1:$A$10,1)
The last argument 1 specifies return the position of the number in the list that is less than the search criteria provided the list is sorted in ascending order.
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
add a comment |
Assuming that as shown in your snapshot, the first 2 column values are non overlapping, you can try this Array Formula.
Sample data is in Cells C3:E7, C & D being first two columns. Enter search value in G3.
Put the following formula in I3.
=IFERROR(INDEX(E3:E7,MIN(IF(G3>=C3:C7,IF(G3<=D3:D7,ROW(C3:C7)-ROW(C2),9^99),9^99))),"Not Found")
Now press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall be automatically enclosed in curly braces to indicate that it's an array formula.
Note the following
- Leave at least one row free above the table to refer the ROW function in Excel
- If you are using Excel version prior to 2007 then remove the outer IFERROR. It may not be available. It just replaces Error with 'Not Found' text.
- Row(C2) is important in above example, adjust to refer the one previous row before the table in your solution.
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
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%2f1388953%2fformula-to-have-a-result-in-a-column-if-the-entered-value-is-in-between-the-two%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
As the two value lists you are comparing against are contiguous (i.e. the second row in column A is one more than the first row column B) you could use an MATCH function on column A without looking at either of the other columns.
=MATCH(120,$A$1:$A$10,1)
The last argument 1 specifies return the position of the number in the list that is less than the search criteria provided the list is sorted in ascending order.
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
add a comment |
As the two value lists you are comparing against are contiguous (i.e. the second row in column A is one more than the first row column B) you could use an MATCH function on column A without looking at either of the other columns.
=MATCH(120,$A$1:$A$10,1)
The last argument 1 specifies return the position of the number in the list that is less than the search criteria provided the list is sorted in ascending order.
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
add a comment |
As the two value lists you are comparing against are contiguous (i.e. the second row in column A is one more than the first row column B) you could use an MATCH function on column A without looking at either of the other columns.
=MATCH(120,$A$1:$A$10,1)
The last argument 1 specifies return the position of the number in the list that is less than the search criteria provided the list is sorted in ascending order.
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
As the two value lists you are comparing against are contiguous (i.e. the second row in column A is one more than the first row column B) you could use an MATCH function on column A without looking at either of the other columns.
=MATCH(120,$A$1:$A$10,1)
The last argument 1 specifies return the position of the number in the list that is less than the search criteria provided the list is sorted in ascending order.
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
answered Dec 30 '18 at 13:29
Mark FitzgeraldMark Fitzgerald
3681211
3681211
add a comment |
add a comment |
Assuming that as shown in your snapshot, the first 2 column values are non overlapping, you can try this Array Formula.
Sample data is in Cells C3:E7, C & D being first two columns. Enter search value in G3.
Put the following formula in I3.
=IFERROR(INDEX(E3:E7,MIN(IF(G3>=C3:C7,IF(G3<=D3:D7,ROW(C3:C7)-ROW(C2),9^99),9^99))),"Not Found")
Now press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall be automatically enclosed in curly braces to indicate that it's an array formula.
Note the following
- Leave at least one row free above the table to refer the ROW function in Excel
- If you are using Excel version prior to 2007 then remove the outer IFERROR. It may not be available. It just replaces Error with 'Not Found' text.
- Row(C2) is important in above example, adjust to refer the one previous row before the table in your solution.
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
add a comment |
Assuming that as shown in your snapshot, the first 2 column values are non overlapping, you can try this Array Formula.
Sample data is in Cells C3:E7, C & D being first two columns. Enter search value in G3.
Put the following formula in I3.
=IFERROR(INDEX(E3:E7,MIN(IF(G3>=C3:C7,IF(G3<=D3:D7,ROW(C3:C7)-ROW(C2),9^99),9^99))),"Not Found")
Now press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall be automatically enclosed in curly braces to indicate that it's an array formula.
Note the following
- Leave at least one row free above the table to refer the ROW function in Excel
- If you are using Excel version prior to 2007 then remove the outer IFERROR. It may not be available. It just replaces Error with 'Not Found' text.
- Row(C2) is important in above example, adjust to refer the one previous row before the table in your solution.
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
add a comment |
Assuming that as shown in your snapshot, the first 2 column values are non overlapping, you can try this Array Formula.
Sample data is in Cells C3:E7, C & D being first two columns. Enter search value in G3.
Put the following formula in I3.
=IFERROR(INDEX(E3:E7,MIN(IF(G3>=C3:C7,IF(G3<=D3:D7,ROW(C3:C7)-ROW(C2),9^99),9^99))),"Not Found")
Now press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall be automatically enclosed in curly braces to indicate that it's an array formula.
Note the following
- Leave at least one row free above the table to refer the ROW function in Excel
- If you are using Excel version prior to 2007 then remove the outer IFERROR. It may not be available. It just replaces Error with 'Not Found' text.
- Row(C2) is important in above example, adjust to refer the one previous row before the table in your solution.
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
Assuming that as shown in your snapshot, the first 2 column values are non overlapping, you can try this Array Formula.
Sample data is in Cells C3:E7, C & D being first two columns. Enter search value in G3.
Put the following formula in I3.
=IFERROR(INDEX(E3:E7,MIN(IF(G3>=C3:C7,IF(G3<=D3:D7,ROW(C3:C7)-ROW(C2),9^99),9^99))),"Not Found")
Now press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall be automatically enclosed in curly braces to indicate that it's an array formula.
Note the following
- Leave at least one row free above the table to refer the ROW function in Excel
- If you are using Excel version prior to 2007 then remove the outer IFERROR. It may not be available. It just replaces Error with 'Not Found' text.
- Row(C2) is important in above example, adjust to refer the one previous row before the table in your solution.
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
edited Dec 30 '18 at 13:31
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
answered Dec 30 '18 at 13:25
patkimpatkim
3,2242723
3,2242723
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%2f1388953%2fformula-to-have-a-result-in-a-column-if-the-entered-value-is-in-between-the-two%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 values in the first 2 columns non overlapping always?
– patkim
Dec 30 '18 at 13:10