How to Find the location of a value in a multi row / column Excel spreadsheet table

Multi tool use
I searched Google for over an hour and could only come up with vlookup, index, and match solutions that do not solve this problem.
I thought it would be simple but I could not find a direct solution for the following:
How do I find either the cell address or preferably the row and column of the value 119, or any other number in the table below?
The table does not contain row or column titles, only the numbers as shown.
I am looking for a worksheet solution (formula) and not a VBA solution.
excel matrix excel-formula lookup
add a comment |
I searched Google for over an hour and could only come up with vlookup, index, and match solutions that do not solve this problem.
I thought it would be simple but I could not find a direct solution for the following:
How do I find either the cell address or preferably the row and column of the value 119, or any other number in the table below?
The table does not contain row or column titles, only the numbers as shown.
I am looking for a worksheet solution (formula) and not a VBA solution.
excel matrix excel-formula lookup
add a comment |
I searched Google for over an hour and could only come up with vlookup, index, and match solutions that do not solve this problem.
I thought it would be simple but I could not find a direct solution for the following:
How do I find either the cell address or preferably the row and column of the value 119, or any other number in the table below?
The table does not contain row or column titles, only the numbers as shown.
I am looking for a worksheet solution (formula) and not a VBA solution.
excel matrix excel-formula lookup
I searched Google for over an hour and could only come up with vlookup, index, and match solutions that do not solve this problem.
I thought it would be simple but I could not find a direct solution for the following:
How do I find either the cell address or preferably the row and column of the value 119, or any other number in the table below?
The table does not contain row or column titles, only the numbers as shown.
I am looking for a worksheet solution (formula) and not a VBA solution.
excel matrix excel-formula lookup
excel matrix excel-formula lookup
edited Dec 30 '18 at 23:11


Brian Tompsett - 汤莱恩
4,2131338101
4,2131338101
asked Dec 30 '18 at 20:53
SammySammy
3491518
3491518
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
An Array Formula
This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.
=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))
Remarks:
The value is searched by column i.e. A1, A2, ... B1, B2 ...
i.e. if you had another 119
in cell D1
the result would still be 2, and if you had a 119
in cell c1
then the result would be
1.
For a column version just replace ROW with COLUMN:
=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
add a comment |
Well, clunky and you can expand it, but it does work:
Row is separate to column but you could put them together in one cell, does depend on how you want to use the results, but you did not specify that so I have done this...
You could use a choose() function or a lookup table with vlookup() to change the column result to a letter...
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
add a comment |
Please try:
=MOD((K1-50),34)+1&" | "&1+(INT((K1-50)/34))
where K1
is your selected value.
Returns R | C
. (Data in A1:J34 is not required.)
1
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
1
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
add a comment |
Below is a general purpose answer based on VBasic2008's answer.
I modified the formulas to utilize defined names so that the cell references do not have to be hard coded in the formulas. This way both the data table and row / column formulas can be relocated to anywhere on the spreadsheet. It works for both numerical AND text based data.
I also included the =ADDRESS() function to return the absolute reference of the look up value.
For illustration purposes, a step by step example for Data Set 1 is shown replacing the hard coded cell references with defined names.
The Data Set 2 section is the simplified version just using one defined for each the row and column look up value.
You can download an example spreadsheet here: Look_Up_a_Value_in_a_Table.xls
Thanks to all of you: Solar Mike, VBasic2008, and pnuts
Click on the image to enlarge.
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%2f53981344%2fhow-to-find-the-location-of-a-value-in-a-multi-row-column-excel-spreadsheet-ta%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
An Array Formula
This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.
=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))
Remarks:
The value is searched by column i.e. A1, A2, ... B1, B2 ...
i.e. if you had another 119
in cell D1
the result would still be 2, and if you had a 119
in cell c1
then the result would be
1.
For a column version just replace ROW with COLUMN:
=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
add a comment |
An Array Formula
This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.
=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))
Remarks:
The value is searched by column i.e. A1, A2, ... B1, B2 ...
i.e. if you had another 119
in cell D1
the result would still be 2, and if you had a 119
in cell c1
then the result would be
1.
For a column version just replace ROW with COLUMN:
=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
add a comment |
An Array Formula
This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.
=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))
Remarks:
The value is searched by column i.e. A1, A2, ... B1, B2 ...
i.e. if you had another 119
in cell D1
the result would still be 2, and if you had a 119
in cell c1
then the result would be
1.
For a column version just replace ROW with COLUMN:
=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))
An Array Formula
This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.
=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))
Remarks:
The value is searched by column i.e. A1, A2, ... B1, B2 ...
i.e. if you had another 119
in cell D1
the result would still be 2, and if you had a 119
in cell c1
then the result would be
1.
For a column version just replace ROW with COLUMN:
=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))
edited Dec 30 '18 at 22:18
answered Dec 30 '18 at 21:57


VBasic2008VBasic2008
2,5912414
2,5912414
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
add a comment |
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
Thank you VBasic2008. This is the selected answer.
– Sammy
Dec 31 '18 at 3:29
add a comment |
Well, clunky and you can expand it, but it does work:
Row is separate to column but you could put them together in one cell, does depend on how you want to use the results, but you did not specify that so I have done this...
You could use a choose() function or a lookup table with vlookup() to change the column result to a letter...
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
add a comment |
Well, clunky and you can expand it, but it does work:
Row is separate to column but you could put them together in one cell, does depend on how you want to use the results, but you did not specify that so I have done this...
You could use a choose() function or a lookup table with vlookup() to change the column result to a letter...
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
add a comment |
Well, clunky and you can expand it, but it does work:
Row is separate to column but you could put them together in one cell, does depend on how you want to use the results, but you did not specify that so I have done this...
You could use a choose() function or a lookup table with vlookup() to change the column result to a letter...
Well, clunky and you can expand it, but it does work:
Row is separate to column but you could put them together in one cell, does depend on how you want to use the results, but you did not specify that so I have done this...
You could use a choose() function or a lookup table with vlookup() to change the column result to a letter...
edited Dec 31 '18 at 8:36
answered Dec 30 '18 at 21:44
Solar MikeSolar Mike
2,1912515
2,1912515
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
add a comment |
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:
=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
I was going to post something similar... I thought you might as well return the row and column at the same time, e.g.:
=IFERROR("A_"&MATCH(E2,A1:A3,0),IFERROR("B_"&MATCH(E2,B1:B3,0),IFERROR("C_"&MATCH(E2,C1:C3,0),"XX")))
– David
Dec 30 '18 at 21:57
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
@David you should still post yours as it is shorter than mine and just as valid...
– Solar Mike
Dec 30 '18 at 22:00
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
An interesting solution. However, could be too involved for a large table. Thanks for your help
– Sammy
Dec 31 '18 at 9:39
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
@Sammy I enjoyed making it work, but there are more elegant solutions which is fine. :)
– Solar Mike
Dec 31 '18 at 9:40
add a comment |
Please try:
=MOD((K1-50),34)+1&" | "&1+(INT((K1-50)/34))
where K1
is your selected value.
Returns R | C
. (Data in A1:J34 is not required.)
1
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
1
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
add a comment |
Please try:
=MOD((K1-50),34)+1&" | "&1+(INT((K1-50)/34))
where K1
is your selected value.
Returns R | C
. (Data in A1:J34 is not required.)
1
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
1
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
add a comment |
Please try:
=MOD((K1-50),34)+1&" | "&1+(INT((K1-50)/34))
where K1
is your selected value.
Returns R | C
. (Data in A1:J34 is not required.)
Please try:
=MOD((K1-50),34)+1&" | "&1+(INT((K1-50)/34))
where K1
is your selected value.
Returns R | C
. (Data in A1:J34 is not required.)
answered Dec 30 '18 at 21:51
pnutspnuts
48.5k76297
48.5k76297
1
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
1
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
add a comment |
1
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
1
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
1
1
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
This works and produces the same results as my, much longer, effort above and demonstrates superb knowledge, plus 1 from me...
– Solar Mike
Dec 30 '18 at 23:33
1
1
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
pnuts: Very cool method indeed. Sorry if my example portrayed a numerical only type problem. I actually need a solution that works for any type of data, not just numbers.
– Sammy
Dec 31 '18 at 9:45
add a comment |
Below is a general purpose answer based on VBasic2008's answer.
I modified the formulas to utilize defined names so that the cell references do not have to be hard coded in the formulas. This way both the data table and row / column formulas can be relocated to anywhere on the spreadsheet. It works for both numerical AND text based data.
I also included the =ADDRESS() function to return the absolute reference of the look up value.
For illustration purposes, a step by step example for Data Set 1 is shown replacing the hard coded cell references with defined names.
The Data Set 2 section is the simplified version just using one defined for each the row and column look up value.
You can download an example spreadsheet here: Look_Up_a_Value_in_a_Table.xls
Thanks to all of you: Solar Mike, VBasic2008, and pnuts
Click on the image to enlarge.
add a comment |
Below is a general purpose answer based on VBasic2008's answer.
I modified the formulas to utilize defined names so that the cell references do not have to be hard coded in the formulas. This way both the data table and row / column formulas can be relocated to anywhere on the spreadsheet. It works for both numerical AND text based data.
I also included the =ADDRESS() function to return the absolute reference of the look up value.
For illustration purposes, a step by step example for Data Set 1 is shown replacing the hard coded cell references with defined names.
The Data Set 2 section is the simplified version just using one defined for each the row and column look up value.
You can download an example spreadsheet here: Look_Up_a_Value_in_a_Table.xls
Thanks to all of you: Solar Mike, VBasic2008, and pnuts
Click on the image to enlarge.
add a comment |
Below is a general purpose answer based on VBasic2008's answer.
I modified the formulas to utilize defined names so that the cell references do not have to be hard coded in the formulas. This way both the data table and row / column formulas can be relocated to anywhere on the spreadsheet. It works for both numerical AND text based data.
I also included the =ADDRESS() function to return the absolute reference of the look up value.
For illustration purposes, a step by step example for Data Set 1 is shown replacing the hard coded cell references with defined names.
The Data Set 2 section is the simplified version just using one defined for each the row and column look up value.
You can download an example spreadsheet here: Look_Up_a_Value_in_a_Table.xls
Thanks to all of you: Solar Mike, VBasic2008, and pnuts
Click on the image to enlarge.
Below is a general purpose answer based on VBasic2008's answer.
I modified the formulas to utilize defined names so that the cell references do not have to be hard coded in the formulas. This way both the data table and row / column formulas can be relocated to anywhere on the spreadsheet. It works for both numerical AND text based data.
I also included the =ADDRESS() function to return the absolute reference of the look up value.
For illustration purposes, a step by step example for Data Set 1 is shown replacing the hard coded cell references with defined names.
The Data Set 2 section is the simplified version just using one defined for each the row and column look up value.
You can download an example spreadsheet here: Look_Up_a_Value_in_a_Table.xls
Thanks to all of you: Solar Mike, VBasic2008, and pnuts
Click on the image to enlarge.
edited Jan 1 at 3:48
answered Dec 31 '18 at 3:26
SammySammy
3491518
3491518
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%2f53981344%2fhow-to-find-the-location-of-a-value-in-a-multi-row-column-excel-spreadsheet-ta%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
wipo6 31IkE3krID,qZ4,mGteUYiKZp Hn,n90Gv3OssMsf6LFDxpb USo3CzzGA,NubvY PUxNXY4,G1lpoTpAbtWa7VMIvIf