Large Function and Cell Address Problem

[Brampton76]

Good Morning,
I have identified the 2 largest numbers in a row using LARGE in a range A1:F1 with the answers in A3 and A4. Then I tried to identify the cell reference for each using using =CELL("address",INDEX($A$1:$F$1,MATCH(A3,$A$1:$F$1,0))) for the 1st and =CELL("address",INDEX($A$1:$F$1,MATCH(A4,$A$1:$F$1,0))) for the 2nd. But it returns $C$1 in both instances and I am not sure why and wondered if someone could explain please? And.......... help me towards a return of $C$1 and $D$1 which is where they both live.

Thank you

 

[Marcelo Branco]

Could you provide an example along with expected results?

M.

 

[Legacy 143009]

Your formulas contain self referring addresses. I didn't get it. Try:

A3=CELL("address",INDEX($A$1:$F$1,MATCH(MAX(A1:F1),$A$1:$F$1,0)))
A4=CELL("address",INDEX($A$1:$F$1,MATCH(LARGE(A1:F1,2),$A$1:$F$1,0)))

 

[Brampton76]

Could you provide an example along with expected results?

M.

Many thanks for looking. Attached:

	A	B	C	D	E	F
$C$1	$C$1
$C$1	$D$1

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]38[/TD]

[TD="align: center"]Expected Result[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]38[/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=LARGE($A$1:$L$1,1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=CELL("address",INDEX($A$1:$L$1,MATCH(A3,$A$1:$L$1,0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]=LARGE($A$1:$L$1,2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=CELL("address",INDEX($A$1:$L$1,MATCH(A4,$A$1:$L$1,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Brampton76]

Your formulas contain self referring addresses. I didn't get it. Try:

A3=CELL("address",INDEX($A$1:$F$1,MATCH(MAX(A1:F1),$A$1:$F$1,0)))
A4=CELL("address",INDEX($A$1:$F$1,MATCH(LARGE(A1:F1,2),$A$1:$F$1,0)))

Flashbond, many thanks for taking the time. Unfortunately, the result is the same, both A3 and A4 show $C$1.

Kind Regards
Glenn

 

[Marcelo Branco]

Try this array formula in B3 copied down
=CELL("address",INDEX(A$1:$F$1,SMALL(IF(A$1:F$1=A3,COLUMN(A$1:F$1)-COLUMN(A$1)+1),COUNTIF(A$3:A3,A3))))
Ctrl+Shift+Enter

M.

 

[Brampton76]

Try this array formula in B3 copied down
=CELL("address",INDEX(A$1:$F$1,SMALL(IF(A$1:F$1=A3,COLUMN(A$1:F$1)-COLUMN(A$1)+1),COUNTIF(A$3:A3,A3))))
Ctrl+Shift+Enter

M.

M. Many thanks, it works a treat, awesome. I hadn't expected to see SMALL or COUNTIF in the formula so I had a look in the Help but I am not quite sure I fully understand the significance in the formula and wondered if you could explain please?

Many thanks
Glenn

 

[Marcelo Branco]

To see what the formula does, step by step, try
Select B3
Formulas > Evaluate formula

A brief explanation about the formula in B3
1. COLUMN(A$1:F$1)-COLUMN(A$1)+1 generates a horizontal array
{1,2,3,4,5.6}

2. The IF(A1:F1=B3, when B3 = 38, yields an array of True and False values like
{False,False,True,True,False,False}
So the IF results in
{False,False,3,4,False,False}

3. COUNTIF(B$3:B3,B3) results in 1

4. SMALL({False,False,3,4,False,False},1) results in 3

5, So INDEX(A1:F1,3) returns a reference to the third cell in the range: C1

6. At last, CELL("address",C1) ---> $C$1 as desired

How it works in B4?
The only difference is that COUNTIF(A$3:A4,A4) results in 2 and the SMALL function returns 4 (the second smallest); INDEX function returns a reference to fourth cell in the range: D1

M.

 

[Marcelo Branco]

Corrections: typos

1. The array should be
{1,2,3,4,5,6}

3. Should be
COUNTIF(A$3:A3,A3)

M.

 

[Brampton76]

M,

Many thanks again for all your help, it is much appreciated.

Kind Regards
Glenn