Excel CHOOSE Value Error
up vote
0
down vote
favorite
I am using MATCH and CHOOSE to populate a table. Basically, I want CHOOSE to return values to an "11 row and 5 column" table from 52 different tables. Formula is below.
=+CHOOSE(MATCH($B$3,List!$M$5:$M$14),SAMS!$C$4:$G$14,SAMS!$C$17:$G$27,SAMS!$C$30:G$40,SAMS!C$43:G$53,SAMS!$C$56:$G$66,SAMS!$C$69:$G$79,SAMS!$C$82:$G$92,SAMS!$C$95:$G$105,SAMS!$C$108:$G$118,SAMS!$C$121:$G$131).
When MATCH returns 1, CHOOSE works. But when it returns any other number, CHOOSE does not work. Can you help? Thanks Ben
microsoft-excel
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
asked Nov 16 at 16:00
Bernard Oche
11
|
show 14 more comments
up vote
0
down vote
favorite
I am using MATCH and CHOOSE to populate a table. Basically, I want CHOOSE to return values to an "11 row and 5 column" table from 52 different tables. Formula is below.
=+CHOOSE(MATCH($B$3,List!$M$5:$M$14),SAMS!$C$4:$G$14,SAMS!$C$17:$G$27,SAMS!$C$30:G$40,SAMS!C$43:G$53,SAMS!$C$56:$G$66,SAMS!$C$69:$G$79,SAMS!$C$82:$G$92,SAMS!$C$95:$G$105,SAMS!$C$108:$G$118,SAMS!$C$121:$G$131).
When MATCH returns 1, CHOOSE works. But when it returns any other number, CHOOSE does not work. Can you help? Thanks Ben
microsoft-excel
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
asked Nov 16 at 16:00
Bernard Oche
11
2
I hope the pending edit is to turn the formula into code{}
– spikey_richie
Nov 16 at 16:16
1
(1) Do you realize that you are missing three$signs? (2) You say that you want to “return values … from 52 different tables”. How is that supposed to happen when you have only 10 choices? If your actual formula lists 52 choices, (2a) thank you for editing it down to 10, but (2b) you should have said that you were presenting incomplete information, and (2c) it would have been nice if you had reduced it even more — say, enough that the formula could fit on one line. … (Cont’d)
– Scott
Nov 16 at 16:52
1
(Cont’d) … (3) Are you sure thatMATCHis returning a valid value? (3a) Have you used “Evaluate Formula”? (3b) Have you tried replacing theMATCHcall with a simple cell reference? (These are basic debugging procedures.) (3c) What debugging have you done? (4) Please don’t post a question saying that something “does not work”. What does it do? … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 16 at 16:52
You're using choose to select a range as a result. Choose selects a single value. But even if it could, how can you fit a range in a single result cell? What you're doing in the formula isn't valid. I'm curious what you get when match returns 1.
– fixer1234
Nov 17 at 7:58
@fixer1234: I got the OP's formula to work (replacing theMATCHcall with a simple cell reference) by applying it to a 5×11 rectangular range and making it an array formula.
– Scott
Nov 19 at 7:40
|
show 14 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am using MATCH and CHOOSE to populate a table. Basically, I want CHOOSE to return values to an "11 row and 5 column" table from 52 different tables. Formula is below.
=+CHOOSE(MATCH($B$3,List!$M$5:$M$14),SAMS!$C$4:$G$14,SAMS!$C$17:$G$27,SAMS!$C$30:G$40,SAMS!C$43:G$53,SAMS!$C$56:$G$66,SAMS!$C$69:$G$79,SAMS!$C$82:$G$92,SAMS!$C$95:$G$105,SAMS!$C$108:$G$118,SAMS!$C$121:$G$131).
When MATCH returns 1, CHOOSE works. But when it returns any other number, CHOOSE does not work. Can you help? Thanks Ben
microsoft-excel
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
asked Nov 16 at 16:00
Bernard Oche
11
I am using MATCH and CHOOSE to populate a table. Basically, I want CHOOSE to return values to an "11 row and 5 column" table from 52 different tables. Formula is below.
=+CHOOSE(MATCH($B$3,List!$M$5:$M$14),SAMS!$C$4:$G$14,SAMS!$C$17:$G$27,SAMS!$C$30:G$40,SAMS!C$43:G$53,SAMS!$C$56:$G$66,SAMS!$C$69:$G$79,SAMS!$C$82:$G$92,SAMS!$C$95:$G$105,SAMS!$C$108:$G$118,SAMS!$C$121:$G$131).
When MATCH returns 1, CHOOSE works. But when it returns any other number, CHOOSE does not work. Can you help? Thanks Ben
microsoft-excel
microsoft-excel
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
asked Nov 16 at 16:00
Bernard Oche
11
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
asked Nov 16 at 16:00
Bernard Oche
11
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
edited Nov 16 at 17:48
cybernetic.nomad
1,093111
1,093111
asked Nov 16 at 16:00
Bernard Oche
11
asked Nov 16 at 16:00
Bernard Oche
11
asked Nov 16 at 16:00
Bernard Oche
11
11
2
I hope the pending edit is to turn the formula into code{}
– spikey_richie
Nov 16 at 16:16
1
(1) Do you realize that you are missing three$signs? (2) You say that you want to “return values … from 52 different tables”. How is that supposed to happen when you have only 10 choices? If your actual formula lists 52 choices, (2a) thank you for editing it down to 10, but (2b) you should have said that you were presenting incomplete information, and (2c) it would have been nice if you had reduced it even more — say, enough that the formula could fit on one line. … (Cont’d)
– Scott
Nov 16 at 16:52
1
(Cont’d) … (3) Are you sure thatMATCHis returning a valid value? (3a) Have you used “Evaluate Formula”? (3b) Have you tried replacing theMATCHcall with a simple cell reference? (These are basic debugging procedures.) (3c) What debugging have you done? (4) Please don’t post a question saying that something “does not work”. What does it do? … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 16 at 16:52
You're using choose to select a range as a result. Choose selects a single value. But even if it could, how can you fit a range in a single result cell? What you're doing in the formula isn't valid. I'm curious what you get when match returns 1.
– fixer1234
Nov 17 at 7:58
@fixer1234: I got the OP's formula to work (replacing theMATCHcall with a simple cell reference) by applying it to a 5×11 rectangular range and making it an array formula.
– Scott
Nov 19 at 7:40
|
show 14 more comments
2
I hope the pending edit is to turn the formula into code{}
– spikey_richie
Nov 16 at 16:16
1
(1) Do you realize that you are missing three$signs? (2) You say that you want to “return values … from 52 different tables”. How is that supposed to happen when you have only 10 choices? If your actual formula lists 52 choices, (2a) thank you for editing it down to 10, but (2b) you should have said that you were presenting incomplete information, and (2c) it would have been nice if you had reduced it even more — say, enough that the formula could fit on one line. … (Cont’d)
– Scott
Nov 16 at 16:52
1
(Cont’d) … (3) Are you sure thatMATCHis returning a valid value? (3a) Have you used “Evaluate Formula”? (3b) Have you tried replacing theMATCHcall with a simple cell reference? (These are basic debugging procedures.) (3c) What debugging have you done? (4) Please don’t post a question saying that something “does not work”. What does it do? … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 16 at 16:52
You're using choose to select a range as a result. Choose selects a single value. But even if it could, how can you fit a range in a single result cell? What you're doing in the formula isn't valid. I'm curious what you get when match returns 1.
– fixer1234
Nov 17 at 7:58
@fixer1234: I got the OP's formula to work (replacing theMATCHcall with a simple cell reference) by applying it to a 5×11 rectangular range and making it an array formula.
– Scott
Nov 19 at 7:40
2
2
I hope the pending edit is to turn the formula into code{}
– spikey_richie
Nov 16 at 16:16
I hope the pending edit is to turn the formula into code{}
– spikey_richie
Nov 16 at 16:16
1
1
(1) Do you realize that you are missing three
$ signs? (2) You say that you want to “return values … from 52 different tables”. How is that supposed to happen when you have only 10 choices? If your actual formula lists 52 choices, (2a) thank you for editing it down to 10, but (2b) you should have said that you were presenting incomplete information, and (2c) it would have been nice if you had reduced it even more — say, enough that the formula could fit on one line. … (Cont’d)– Scott
Nov 16 at 16:52
(1) Do you realize that you are missing three
$ signs? (2) You say that you want to “return values … from 52 different tables”. How is that supposed to happen when you have only 10 choices? If your actual formula lists 52 choices, (2a) thank you for editing it down to 10, but (2b) you should have said that you were presenting incomplete information, and (2c) it would have been nice if you had reduced it even more — say, enough that the formula could fit on one line. … (Cont’d)– Scott
Nov 16 at 16:52
1
1
(Cont’d) … (3) Are you sure that
MATCH is returning a valid value? (3a) Have you used “Evaluate Formula”? (3b) Have you tried replacing the MATCH call with a simple cell reference? (These are basic debugging procedures.) (3c) What debugging have you done? (4) Please don’t post a question saying that something “does not work”. What does it do? … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.– Scott
Nov 16 at 16:52
(Cont’d) … (3) Are you sure that
MATCH is returning a valid value? (3a) Have you used “Evaluate Formula”? (3b) Have you tried replacing the MATCH call with a simple cell reference? (These are basic debugging procedures.) (3c) What debugging have you done? (4) Please don’t post a question saying that something “does not work”. What does it do? … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.– Scott
Nov 16 at 16:52
You're using choose to select a range as a result. Choose selects a single value. But even if it could, how can you fit a range in a single result cell? What you're doing in the formula isn't valid. I'm curious what you get when match returns 1.
– fixer1234
Nov 17 at 7:58
You're using choose to select a range as a result. Choose selects a single value. But even if it could, how can you fit a range in a single result cell? What you're doing in the formula isn't valid. I'm curious what you get when match returns 1.
– fixer1234
Nov 17 at 7:58
@fixer1234: I got the OP's formula to work (replacing the
MATCH call with a simple cell reference) by applying it to a 5×11 rectangular range and making it an array formula.– Scott
Nov 19 at 7:40
@fixer1234: I got the OP's formula to work (replacing the
MATCH call with a simple cell reference) by applying it to a 5×11 rectangular range and making it an array formula.– Scott
Nov 19 at 7:40
|
show 14 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
draft saved
draft discarded
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%2f1376032%2fexcel-choose-value-error%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
2
I hope the pending edit is to turn the formula into code{}
– spikey_richie
Nov 16 at 16:16
1
(1) Do you realize that you are missing three
$signs? (2) You say that you want to “return values … from 52 different tables”. How is that supposed to happen when you have only 10 choices? If your actual formula lists 52 choices, (2a) thank you for editing it down to 10, but (2b) you should have said that you were presenting incomplete information, and (2c) it would have been nice if you had reduced it even more — say, enough that the formula could fit on one line. … (Cont’d)– Scott
Nov 16 at 16:52
1
(Cont’d) … (3) Are you sure that
MATCHis returning a valid value? (3a) Have you used “Evaluate Formula”? (3b) Have you tried replacing theMATCHcall with a simple cell reference? (These are basic debugging procedures.) (3c) What debugging have you done? (4) Please don’t post a question saying that something “does not work”. What does it do? … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.– Scott
Nov 16 at 16:52
You're using choose to select a range as a result. Choose selects a single value. But even if it could, how can you fit a range in a single result cell? What you're doing in the formula isn't valid. I'm curious what you get when match returns 1.
– fixer1234
Nov 17 at 7:58
@fixer1234: I got the OP's formula to work (replacing the
MATCHcall with a simple cell reference) by applying it to a 5×11 rectangular range and making it an array formula.– Scott
Nov 19 at 7:40