Formula to search for an integer value in a range (2d grid)
I have a grid of cells/range/table containing integer values, e.g., A1 to E10.
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
I can see how to search for a value in a single column/row, but not in a range/grid/table.
eg look for value 99 which could be in any column A to E and any row 1 to 10
Sample grid of numbers (5 rows), not sorted, some repeating numbers.
......A.. .B.. ..C.. .D.. ..E
1.. 11.. 21.. 51.. 77.. 11
2.. 12.. 11.. 55.. 23.. 89
3.. 15.. 33.. 01.. 55.. 10
4.. 19.. 15.. 02.. 05.. 01
5.. 21.. 42.. 07.. 12.. 23
(Grid of integers, dots and leading zeros are there just to try and get a rough grid layout)
microsoft-excel
asked Dec 24 '18 at 18:28
johncjohnc
184111
add a comment |
I have a grid of cells/range/table containing integer values, e.g., A1 to E10.
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
I can see how to search for a value in a single column/row, but not in a range/grid/table.
eg look for value 99 which could be in any column A to E and any row 1 to 10
Sample grid of numbers (5 rows), not sorted, some repeating numbers.
......A.. .B.. ..C.. .D.. ..E
1.. 11.. 21.. 51.. 77.. 11
2.. 12.. 11.. 55.. 23.. 89
3.. 15.. 33.. 01.. 55.. 10
4.. 19.. 15.. 02.. 05.. 01
5.. 21.. 42.. 07.. 12.. 23
(Grid of integers, dots and leading zeros are there just to try and get a rough grid layout)
microsoft-excel
asked Dec 24 '18 at 18:28
johncjohnc
184111
This is basic stuff. VLOOKUP function - Office Support
– DavidPostill♦
Dec 24 '18 at 18:33
Could you provide a sample about this problem?
– Lee
Dec 25 '18 at 5:19
Lee, I've just added a sample grid. I'm trying to do a find using formulae over the grid not just one row or column
– johnc
Dec 25 '18 at 16:02
add a comment |
I have a grid of cells/range/table containing integer values, e.g., A1 to E10.
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
I can see how to search for a value in a single column/row, but not in a range/grid/table.
eg look for value 99 which could be in any column A to E and any row 1 to 10
Sample grid of numbers (5 rows), not sorted, some repeating numbers.
......A.. .B.. ..C.. .D.. ..E
1.. 11.. 21.. 51.. 77.. 11
2.. 12.. 11.. 55.. 23.. 89
3.. 15.. 33.. 01.. 55.. 10
4.. 19.. 15.. 02.. 05.. 01
5.. 21.. 42.. 07.. 12.. 23
(Grid of integers, dots and leading zeros are there just to try and get a rough grid layout)
microsoft-excel
asked Dec 24 '18 at 18:28
johncjohnc
184111
I have a grid of cells/range/table containing integer values, e.g., A1 to E10.
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
I can see how to search for a value in a single column/row, but not in a range/grid/table.
eg look for value 99 which could be in any column A to E and any row 1 to 10
Sample grid of numbers (5 rows), not sorted, some repeating numbers.
......A.. .B.. ..C.. .D.. ..E
1.. 11.. 21.. 51.. 77.. 11
2.. 12.. 11.. 55.. 23.. 89
3.. 15.. 33.. 01.. 55.. 10
4.. 19.. 15.. 02.. 05.. 01
5.. 21.. 42.. 07.. 12.. 23
(Grid of integers, dots and leading zeros are there just to try and get a rough grid layout)
microsoft-excel
microsoft-excel
asked Dec 24 '18 at 18:28
johncjohnc
184111
asked Dec 24 '18 at 18:28
johncjohnc
184111
edited Dec 25 '18 at 15:56
johnc
asked Dec 24 '18 at 18:28
johncjohnc
184111
asked Dec 24 '18 at 18:28
johncjohnc
184111
asked Dec 24 '18 at 18:28
johncjohnc
184111
184111
This is basic stuff. VLOOKUP function - Office Support
– DavidPostill♦
Dec 24 '18 at 18:33
Could you provide a sample about this problem?
– Lee
Dec 25 '18 at 5:19
Lee, I've just added a sample grid. I'm trying to do a find using formulae over the grid not just one row or column
– johnc
Dec 25 '18 at 16:02
add a comment |
This is basic stuff. VLOOKUP function - Office Support
– DavidPostill♦
Dec 24 '18 at 18:33
Could you provide a sample about this problem?
– Lee
Dec 25 '18 at 5:19
Lee, I've just added a sample grid. I'm trying to do a find using formulae over the grid not just one row or column
– johnc
Dec 25 '18 at 16:02
This is basic stuff. VLOOKUP function - Office Support
– DavidPostill♦
Dec 24 '18 at 18:33
This is basic stuff. VLOOKUP function - Office Support
– DavidPostill♦
Dec 24 '18 at 18:33
Could you provide a sample about this problem?
– Lee
Dec 25 '18 at 5:19
Could you provide a sample about this problem?
– Lee
Dec 25 '18 at 5:19
Lee, I've just added a sample grid. I'm trying to do a find using formulae over the grid not just one row or column
– johnc
Dec 25 '18 at 16:02
Lee, I've just added a sample grid. I'm trying to do a find using formulae over the grid not just one row or column
– johnc
Dec 25 '18 at 16:02
add a comment |
2 Answers
2
active
oldest
votes
I'm sure that you are looking for this one:
This Array Formula will find, Row position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)-ROW($A$1)+1), ROW(1:1))}
And this finds Column position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)+COLUMN($A$1)+0), ROW(1:1))}
Note:
- Finish both Formula with Ctrl+Shift+Enter.
- Adjust cell references in the formula as needed.
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
add a comment |
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
Assuming that the reason you want the "location" of the value X is because you're planning to nest it inside of a second formula to use that location to look up a value in another column... then what you actually want is VLOOKUP. This will let you do it in one function instead of two.
Given a grid like this:
You would use the formula =VLOOKUP("X", A1:E10, 5, FALSE).
Your first argument, "X", is the value in the first column that you want to reference.
Your second argument, A1:E10, is the range in which you are looking up a value.
Your third argument, 5, is the column number in that range (E is the 5th column in the A through E range).
Your fourth argument is whether or not to look for an exact match.
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
|
show 2 more comments
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%2f1387440%2fformula-to-search-for-an-integer-value-in-a-range-2d-grid%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
I'm sure that you are looking for this one:
This Array Formula will find, Row position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)-ROW($A$1)+1), ROW(1:1))}
And this finds Column position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)+COLUMN($A$1)+0), ROW(1:1))}
Note:
- Finish both Formula with Ctrl+Shift+Enter.
- Adjust cell references in the formula as needed.
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
add a comment |
I'm sure that you are looking for this one:
This Array Formula will find, Row position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)-ROW($A$1)+1), ROW(1:1))}
And this finds Column position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)+COLUMN($A$1)+0), ROW(1:1))}
Note:
- Finish both Formula with Ctrl+Shift+Enter.
- Adjust cell references in the formula as needed.
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
add a comment |
I'm sure that you are looking for this one:
This Array Formula will find, Row position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)-ROW($A$1)+1), ROW(1:1))}
And this finds Column position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)+COLUMN($A$1)+0), ROW(1:1))}
Note:
- Finish both Formula with Ctrl+Shift+Enter.
- Adjust cell references in the formula as needed.
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
I'm sure that you are looking for this one:
This Array Formula will find, Row position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)-ROW($A$1)+1), ROW(1:1))}
And this finds Column position:
{=SMALL(IF($A$7=$A$1:$D$5, ROW($A$1:$D$5)+COLUMN($A$1)+0), ROW(1:1))}
Note:
- Finish both Formula with Ctrl+Shift+Enter.
- Adjust cell references in the formula as needed.
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
answered Dec 26 '18 at 11:05
Rajesh SRajesh S
1
1
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
add a comment |
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
Thanks for the introduction to array formulas, very helpful
– johnc
Dec 27 '18 at 10:33
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
@johnc,, glad to help you keep asking ☺
– Rajesh S
Dec 29 '18 at 5:48
add a comment |
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
Assuming that the reason you want the "location" of the value X is because you're planning to nest it inside of a second formula to use that location to look up a value in another column... then what you actually want is VLOOKUP. This will let you do it in one function instead of two.
Given a grid like this:
You would use the formula =VLOOKUP("X", A1:E10, 5, FALSE).
Your first argument, "X", is the value in the first column that you want to reference.
Your second argument, A1:E10, is the range in which you are looking up a value.
Your third argument, 5, is the column number in that range (E is the 5th column in the A through E range).
Your fourth argument is whether or not to look for an exact match.
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
|
show 2 more comments
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
Assuming that the reason you want the "location" of the value X is because you're planning to nest it inside of a second formula to use that location to look up a value in another column... then what you actually want is VLOOKUP. This will let you do it in one function instead of two.
Given a grid like this:
You would use the formula =VLOOKUP("X", A1:E10, 5, FALSE).
Your first argument, "X", is the value in the first column that you want to reference.
Your second argument, A1:E10, is the range in which you are looking up a value.
Your third argument, 5, is the column number in that range (E is the 5th column in the A through E range).
Your fourth argument is whether or not to look for an exact match.
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
|
show 2 more comments
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
Assuming that the reason you want the "location" of the value X is because you're planning to nest it inside of a second formula to use that location to look up a value in another column... then what you actually want is VLOOKUP. This will let you do it in one function instead of two.
Given a grid like this:
You would use the formula =VLOOKUP("X", A1:E10, 5, FALSE).
Your first argument, "X", is the value in the first column that you want to reference.
Your second argument, A1:E10, is the range in which you are looking up a value.
Your third argument, 5, is the column number in that range (E is the 5th column in the A through E range).
Your fourth argument is whether or not to look for an exact match.
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range.
Assuming that the reason you want the "location" of the value X is because you're planning to nest it inside of a second formula to use that location to look up a value in another column... then what you actually want is VLOOKUP. This will let you do it in one function instead of two.
Given a grid like this:
You would use the formula =VLOOKUP("X", A1:E10, 5, FALSE).
Your first argument, "X", is the value in the first column that you want to reference.
Your second argument, A1:E10, is the range in which you are looking up a value.
Your third argument, 5, is the column number in that range (E is the 5th column in the A through E range).
Your fourth argument is whether or not to look for an exact match.
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
answered Dec 24 '18 at 19:03
James DunnJames Dunn
141111
141111
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
|
show 2 more comments
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Thanks for the quick and helpful response. Unfortunately my question was not as clear as should have been. I'm trying to be search for a value eg 99 in any column A to E (row 1 to 10).
– johnc
Dec 24 '18 at 19:21
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Hmmmm, so in this case, if you wanted X, you would actually want to return a location, as in A2?
– James Dunn
Dec 24 '18 at 19:27
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
Yes, that's right.
– johnc
Dec 24 '18 at 19:34
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
What exactly do you want to do with the location? Plug it into another formula? Or just display it and not actually do anything else with it?
– James Dunn
Dec 24 '18 at 19:43
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
I guess this is what you actually want: exceljet.net/formula/get-address-of-lookup-result
– James Dunn
Dec 24 '18 at 19:54
|
show 2 more comments
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1387440%2fformula-to-search-for-an-integer-value-in-a-range-2d-grid%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
This is basic stuff. VLOOKUP function - Office Support
– DavidPostill♦
Dec 24 '18 at 18:33
Could you provide a sample about this problem?
– Lee
Dec 25 '18 at 5:19
Lee, I've just added a sample grid. I'm trying to do a find using formulae over the grid not just one row or column
– johnc
Dec 25 '18 at 16:02