Mixing
Excel - How to find output from two separate look ups and between values
I am trying to find the output (banding in the below example) based on the combination of the values in the first column (A1 and B2) and another number that falls between the min and max banding.
It's hard for me to explain but trying an example, say the first column (A1 and B2) are car brands. For a car brand that is A1 and priced at 2, I would expect the output to be AAA banding. For a car brand that is B2 and priced at 9 I would expect the output to be CCC banding.
How can I populate the output using Excel formulas, I have looked into an Index-Match function but I am currently stumped given it is difficult since the banding might be the same, but the values that fall between them differ between car brands.
excel indexing match vlookup
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
add a comment |
I am trying to find the output (banding in the below example) based on the combination of the values in the first column (A1 and B2) and another number that falls between the min and max banding.
It's hard for me to explain but trying an example, say the first column (A1 and B2) are car brands. For a car brand that is A1 and priced at 2, I would expect the output to be AAA banding. For a car brand that is B2 and priced at 9 I would expect the output to be CCC banding.
How can I populate the output using Excel formulas, I have looked into an Index-Match function but I am currently stumped given it is difficult since the banding might be the same, but the values that fall between them differ between car brands.
excel indexing match vlookup
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
add a comment |
I am trying to find the output (banding in the below example) based on the combination of the values in the first column (A1 and B2) and another number that falls between the min and max banding.
It's hard for me to explain but trying an example, say the first column (A1 and B2) are car brands. For a car brand that is A1 and priced at 2, I would expect the output to be AAA banding. For a car brand that is B2 and priced at 9 I would expect the output to be CCC banding.
How can I populate the output using Excel formulas, I have looked into an Index-Match function but I am currently stumped given it is difficult since the banding might be the same, but the values that fall between them differ between car brands.
excel indexing match vlookup
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
I am trying to find the output (banding in the below example) based on the combination of the values in the first column (A1 and B2) and another number that falls between the min and max banding.
It's hard for me to explain but trying an example, say the first column (A1 and B2) are car brands. For a car brand that is A1 and priced at 2, I would expect the output to be AAA banding. For a car brand that is B2 and priced at 9 I would expect the output to be CCC banding.
How can I populate the output using Excel formulas, I have looked into an Index-Match function but I am currently stumped given it is difficult since the banding might be the same, but the values that fall between them differ between car brands.
excel indexing match vlookup
excel indexing match vlookup
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
asked Nov 20 '18 at 2:14
isuckatcodingisuckatcoding
163
163
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Use following array formula. Array formula means you have to press CTRL+SHIFT+ENTER after typing formula in cell.
=INDEX(D2:D9,MIN(IF(A2:A9=F2,IF(B2:B9<=G2,IF(C2:C9>=G2,ROW(D2:D9)-ROW(D1))))))
Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.
If you prefer non array formula then you can use this formula.
=INDEX(D2:D9,AGGREGATE(15,6,(ROW(D2:D9)-ROW(D1))/((A2:A9=F2)*(B2:B9<=G2)*(C2:C9>=G2)),1))
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
add a comment |
You'll want to do what's known as a double VLOOKUP: VLOOKUP with two criteria?
There are a couple of options there, and be wary that the INDEX(MATCH()) method uses array formulas.
Here is an example of what a formula would look like:
=INDEX(D2:D9,SUMPRODUCT(MATCH(1,(A2:A9=[car])*(B2:B9<=[price])*(C2:C9>[price]),0)))
Just replace [car] and [price] with cell references (or hard-coded values).
answered Nov 20 '18 at 2:38
hughghughg
1818
add a comment |
Index/Match is possible without array formulas. Compare the formula with the screenshot.
=INDEX(INDEX(D:D,MATCH($H$2,A:A,0)):INDEX(D:D,MATCH($H$2,A:A,1)),MATCH($H$3,INDEX(B:B,MATCH($H$2,A:A,0)):INDEX(B:B,MATCH($H$2,A:A,1)),1))
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
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%2f53385269%2fexcel-how-to-find-output-from-two-separate-look-ups-and-between-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use following array formula. Array formula means you have to press CTRL+SHIFT+ENTER after typing formula in cell.
=INDEX(D2:D9,MIN(IF(A2:A9=F2,IF(B2:B9<=G2,IF(C2:C9>=G2,ROW(D2:D9)-ROW(D1))))))
Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.
If you prefer non array formula then you can use this formula.
=INDEX(D2:D9,AGGREGATE(15,6,(ROW(D2:D9)-ROW(D1))/((A2:A9=F2)*(B2:B9<=G2)*(C2:C9>=G2)),1))
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
add a comment |
Use following array formula. Array formula means you have to press CTRL+SHIFT+ENTER after typing formula in cell.
=INDEX(D2:D9,MIN(IF(A2:A9=F2,IF(B2:B9<=G2,IF(C2:C9>=G2,ROW(D2:D9)-ROW(D1))))))
Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.
If you prefer non array formula then you can use this formula.
=INDEX(D2:D9,AGGREGATE(15,6,(ROW(D2:D9)-ROW(D1))/((A2:A9=F2)*(B2:B9<=G2)*(C2:C9>=G2)),1))
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
add a comment |
Use following array formula. Array formula means you have to press CTRL+SHIFT+ENTER after typing formula in cell.
=INDEX(D2:D9,MIN(IF(A2:A9=F2,IF(B2:B9<=G2,IF(C2:C9>=G2,ROW(D2:D9)-ROW(D1))))))
Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.
If you prefer non array formula then you can use this formula.
=INDEX(D2:D9,AGGREGATE(15,6,(ROW(D2:D9)-ROW(D1))/((A2:A9=F2)*(B2:B9<=G2)*(C2:C9>=G2)),1))
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
Use following array formula. Array formula means you have to press CTRL+SHIFT+ENTER after typing formula in cell.
=INDEX(D2:D9,MIN(IF(A2:A9=F2,IF(B2:B9<=G2,IF(C2:C9>=G2,ROW(D2:D9)-ROW(D1))))))
Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.
If you prefer non array formula then you can use this formula.
=INDEX(D2:D9,AGGREGATE(15,6,(ROW(D2:D9)-ROW(D1))/((A2:A9=F2)*(B2:B9<=G2)*(C2:C9>=G2)),1))
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
edited Nov 20 '18 at 2:42
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
answered Nov 20 '18 at 2:35
Harun24HRHarun24HR
3,7382717
3,7382717
add a comment |
add a comment |
You'll want to do what's known as a double VLOOKUP: VLOOKUP with two criteria?
There are a couple of options there, and be wary that the INDEX(MATCH()) method uses array formulas.
Here is an example of what a formula would look like:
=INDEX(D2:D9,SUMPRODUCT(MATCH(1,(A2:A9=[car])*(B2:B9<=[price])*(C2:C9>[price]),0)))
Just replace [car] and [price] with cell references (or hard-coded values).
answered Nov 20 '18 at 2:38
hughghughg
1818
add a comment |
You'll want to do what's known as a double VLOOKUP: VLOOKUP with two criteria?
There are a couple of options there, and be wary that the INDEX(MATCH()) method uses array formulas.
Here is an example of what a formula would look like:
=INDEX(D2:D9,SUMPRODUCT(MATCH(1,(A2:A9=[car])*(B2:B9<=[price])*(C2:C9>[price]),0)))
Just replace [car] and [price] with cell references (or hard-coded values).
answered Nov 20 '18 at 2:38
hughghughg
1818
add a comment |
You'll want to do what's known as a double VLOOKUP: VLOOKUP with two criteria?
There are a couple of options there, and be wary that the INDEX(MATCH()) method uses array formulas.
Here is an example of what a formula would look like:
=INDEX(D2:D9,SUMPRODUCT(MATCH(1,(A2:A9=[car])*(B2:B9<=[price])*(C2:C9>[price]),0)))
Just replace [car] and [price] with cell references (or hard-coded values).
answered Nov 20 '18 at 2:38
hughghughg
1818
You'll want to do what's known as a double VLOOKUP: VLOOKUP with two criteria?
There are a couple of options there, and be wary that the INDEX(MATCH()) method uses array formulas.
Here is an example of what a formula would look like:
=INDEX(D2:D9,SUMPRODUCT(MATCH(1,(A2:A9=[car])*(B2:B9<=[price])*(C2:C9>[price]),0)))
Just replace [car] and [price] with cell references (or hard-coded values).
answered Nov 20 '18 at 2:38
hughghughg
1818
answered Nov 20 '18 at 2:38
hughghughg
1818
answered Nov 20 '18 at 2:38
hughghughg
1818
answered Nov 20 '18 at 2:38
hughghughg
1818
1818
add a comment |
add a comment |
Index/Match is possible without array formulas. Compare the formula with the screenshot.
=INDEX(INDEX(D:D,MATCH($H$2,A:A,0)):INDEX(D:D,MATCH($H$2,A:A,1)),MATCH($H$3,INDEX(B:B,MATCH($H$2,A:A,0)):INDEX(B:B,MATCH($H$2,A:A,1)),1))
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
add a comment |
Index/Match is possible without array formulas. Compare the formula with the screenshot.
=INDEX(INDEX(D:D,MATCH($H$2,A:A,0)):INDEX(D:D,MATCH($H$2,A:A,1)),MATCH($H$3,INDEX(B:B,MATCH($H$2,A:A,0)):INDEX(B:B,MATCH($H$2,A:A,1)),1))
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
add a comment |
Index/Match is possible without array formulas. Compare the formula with the screenshot.
=INDEX(INDEX(D:D,MATCH($H$2,A:A,0)):INDEX(D:D,MATCH($H$2,A:A,1)),MATCH($H$3,INDEX(B:B,MATCH($H$2,A:A,0)):INDEX(B:B,MATCH($H$2,A:A,1)),1))
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
Index/Match is possible without array formulas. Compare the formula with the screenshot.
=INDEX(INDEX(D:D,MATCH($H$2,A:A,0)):INDEX(D:D,MATCH($H$2,A:A,1)),MATCH($H$3,INDEX(B:B,MATCH($H$2,A:A,0)):INDEX(B:B,MATCH($H$2,A:A,1)),1))
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
answered Nov 20 '18 at 2:46
teylynteylyn
22k33453
22k33453
add a comment |
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%2f53385269%2fexcel-how-to-find-output-from-two-separate-look-ups-and-between-values%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
