Formula to find cell address.
- Thread starter getpras
- Start date
NARAYANK991
Excel Ninja
Hi ,
Try this :
=ADDRESS(MATCH(A10,A1:A9,0),1)
Narayan
Try this :
=ADDRESS(MATCH(A10,A1:A9,0),1)
Narayan
NARAYANK991
Excel Ninja
Hi ,
Check your cell formatting in B10 ; if the cell formatting is center across selection , or if it was left aligned and indented to the right several times , then this can happen.
To check this , place your cursor in B10 , and press F2 ; if you see the formula , it means the problem is with the cell formatting.
Narayan
Check your cell formatting in B10 ; if the cell formatting is center across selection , or if it was left aligned and indented to the right several times , then this can happen.
To check this , place your cursor in B10 , and press F2 ; if you see the formula , it means the problem is with the cell formatting.
Narayan
Narayan, you were Right.... it was the formatting.
But now there is another problem. When applied this to another range for E.g. if i need to find the max in range b1:d9 and place the result in D10 and then in E10 i paste your formula it does not work... it does not give me the correct cell address...
But now there is another problem. When applied this to another range for E.g. if i need to find the max in range b1:d9 and place the result in D10 and then in E10 i paste your formula it does not work... it does not give me the correct cell address...
NARAYANK991
Excel Ninja
Hi ,
The original formula , which I am reproducing below , is :
=ADDRESS(MATCH(A10,A1:A9,0),1)
This depends on :
1. The range of values being defined in A1:A9
2. The maximum of the above range being calculated in A10
In the above formula , the ADDRESS function has two parameters ; the first one is the row number , and the second is the column number. A row number of 1 , and a column number of 1 will refer to the cell address $A$1. A row number of 7 , and a column number of 11 , will refer to the address $K$7 , since column K is the 11th column.
If I rephrase the above formula as :
=ADDRESS(MATCH(A10,A1:A9,0),COLUMN(A1))
then the column number will also change when you copy this formula elsewhere.
However , you have now introduced a new factor ; the MATCH function matches only along a row or a column , and not on a multiple rows , multiple columns range , such as B1:D9.
If you really wish to do this over a range , then the formula needs to be revised as follows :
=ADDRESS(MAX((B1:D9=D10)*ROW(B1:D9)),MAX((B1:D9=D10)*COLUMN(B1:D9)))
Narayan
The original formula , which I am reproducing below , is :
=ADDRESS(MATCH(A10,A1:A9,0),1)
This depends on :
1. The range of values being defined in A1:A9
2. The maximum of the above range being calculated in A10
In the above formula , the ADDRESS function has two parameters ; the first one is the row number , and the second is the column number. A row number of 1 , and a column number of 1 will refer to the cell address $A$1. A row number of 7 , and a column number of 11 , will refer to the address $K$7 , since column K is the 11th column.
If I rephrase the above formula as :
=ADDRESS(MATCH(A10,A1:A9,0),COLUMN(A1))
then the column number will also change when you copy this formula elsewhere.
However , you have now introduced a new factor ; the MATCH function matches only along a row or a column , and not on a multiple rows , multiple columns range , such as B1:D9.
If you really wish to do this over a range , then the formula needs to be revised as follows :
=ADDRESS(MAX((B1:D9=D10)*ROW(B1:D9)),MAX((B1:D9=D10)*COLUMN(B1:D9)))
Narayan
anupamtiwari05
Member
Hi Narayan,
I have tried your above Array Formula "=ADDRESS(MAX((B1:D9=D10)*ROW(B1:D9)),MAX((B1:D9=D10)*COLUMN(B1:D9)))", its not showing me the correct address of the cell if the range has more then one MAX value e.g B6 = 100 and C2 = 100, in this case it gives the output as $C$6 but the actual value in C6 is 86 however the above formula is working fine if there is only one unique MAX value in the range.
My range values are as below:
[pre]
[/pre]
Please advise.
Thanks & Regards,
Anupa Tiwari
I have tried your above Array Formula "=ADDRESS(MAX((B1:D9=D10)*ROW(B1:D9)),MAX((B1:D9=D10)*COLUMN(B1:D9)))", its not showing me the correct address of the cell if the range has more then one MAX value e.g B6 = 100 and C2 = 100, in this case it gives the output as $C$6 but the actual value in C6 is 86 however the above formula is working fine if there is only one unique MAX value in the range.
My range values are as below:
[pre]
Code:
B C D
13 29 14
73 100 85
77 65 41
58 56 52
30 21 84
100 86 60
13 30 16
40 84 49
68 66 37Please advise.
Thanks & Regards,
Anupa Tiwari
NARAYANK991
Excel Ninja
Hi Anupam ,
You are right.
Suppose one maximum is in B17 , and the other is in C7 , then the maximum row will be 17 , while the maximum column will be C , which means the end result will be $C$17 , which is obviously wrong.
Narayan
You are right.
Suppose one maximum is in B17 , and the other is in C7 , then the maximum row will be 17 , while the maximum column will be C , which means the end result will be $C$17 , which is obviously wrong.
Narayan
Just curious, but is there a specific reason for wanting the cell address? If it's to be used in another function, we'd do better to not return the string address.
Anyway, I believe this formula will for for a multiple sized array with more than 1 max value. Note that I'm assuming there's a formula already in D10 to find MAX value.
=ADDRESS(MID(MAX(IF(B1:D9=D10,VALUE(900&TEXT(COLUMN(B1:D9),"000")&ROW(B1:D9)))),7,999),
MID(MAX(IF(B1:D9=D10,VALUE(900&TEXT(COLUMN(B1:D9),"000")&ROW(B1:D9)))),4,3))
What it does:
The portion that repeats is there to generate a text string containing the column number and row number of all cells that match the MAX value. Since I'm controlling the syntax of the text string, I can then use the MID function to extract the parts that I want to get the correct row and column to feed into the ADDRESS function.
Anyway, I believe this formula will for for a multiple sized array with more than 1 max value. Note that I'm assuming there's a formula already in D10 to find MAX value.
=ADDRESS(MID(MAX(IF(B1:D9=D10,VALUE(900&TEXT(COLUMN(B1:D9),"000")&ROW(B1:D9)))),7,999),
MID(MAX(IF(B1:D9=D10,VALUE(900&TEXT(COLUMN(B1:D9),"000")&ROW(B1:D9)))),4,3))
What it does:
The portion that repeats is there to generate a text string containing the column number and row number of all cells that match the MAX value. Since I'm controlling the syntax of the text string, I can then use the MID function to extract the parts that I want to get the correct row and column to feed into the ADDRESS function.
anupamtiwari05
Member
Hi Luke,
Thanks a lot for your explaination.
Your formula is working nice and giving me last MAX value in a particular column.
However,I will give time to understand each part of your formula as its complicated for me to understand it at once.
If I find any problem, I will get back to you Luke to take your help again.
Thanks,
Anupam Tiwari
Thanks a lot for your explaination.
Your formula is working nice and giving me last MAX value in a particular column.
However,I will give time to understand each part of your formula as its complicated for me to understand it at once.
If I find any problem, I will get back to you Luke to take your help again.
Thanks,
Anupam Tiwari