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
1	29	34	38	38	30	25
2
3	38	$C$1	Expected Result	$C$1
4	38	$C$1		$D$1

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A3 =LARGE($A$1:$L$1,1) B3 =CELL("address",INDEX($A$1:$L$1,MATCH(A3,$A$1:$L$1,0))) A4 =LARGE($A$1:$L$1,2) B4 =CELL("address",INDEX($A$1:$L$1,MATCH(A4,$A$1:$L$1,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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