Select minimum with duplicate values to return column headings
 

Hi
first post here, hopefully someone can help.

I have a spreadsheet with a set of values attached to names, in rows.

I want to choose the 3 lowest values in each row and return the column headings and have set up these formulae in cells BP-BR.
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,1),$ AJ101:$BN101,0))
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,2),$ AJ101:$BN101,0))
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,3),$ AJ101:$BN101,0))
which works fine if all values are different.
However if 2 or 3 columns have the same value all 3 cells return the same column heading.

How can I get the formula to return the heading from the 2nd and 3rd occurrence of minimum values?

TIA

Select minimum with duplicate values to return column headings
 

hi Tia,

following formulas are array formulas to validate with ctrl + shift + enter


the first lowest values address
=ADDRESS(1,SMALL(IF($AJ$101:$BN$101=SMALL($AJ$101: $BN$101,1),COLUMN($AJ$101:$BN$101)),1))

the second lowest values address
=ADDRESS(1,SMALL(IF($AJ$101:$BN$101=SMALL($AJ$101: $BN$101,2),COLUMN($AJ$101:$BN$101)),2))

the third lowest values address
=ADDRESS(1,SMALL(IF($AJ$101:$BN$101=SMALL($AJ$101: $BN$101,3),COLUMN($AJ$101:$BN$101)),3))

you can add INDIRECT function to get the value instead the address

--
isabelle



Le 2012-04-01 04:51, Bobblebee a écrit :
Hi
first post here, hopefully someone can help.

I have a spreadsheet with a set of values attached to names, in rows.

I want to choose the 3 lowest values in each row and return the column
headings and have set up these formulae in cells BP-BR.
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,1),$ AJ101:$BN101,0))
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,2),$ AJ101:$BN101,0))
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,3),$ AJ101:$BN101,0))
which works fine if all values are different.
However if 2 or 3 columns have the same value all 3 cells return the
same column heading.

How can I get the formula to return the heading from the 2nd and 3rd
occurrence of minimum values?

TIA

Thanks for the reply

I have tried to do this but only get #value! if I do.
I am looking at columns AJ to BN, but all rows, not just 101 [that was just the formula I pasted here]

I have never used an array formula and don't know what I "ctrl + shift + enter"

Thanks for trying to help.

Select minimum with duplicate values to return column headings
 

to validate a formula, normally, we must press enter, but to validate an array formula,
we have to press ctrl + shift + enter simultaneously.
if you did it successfully, you will see in the formula bar,
there are curly brackets that are added on each side of the formula

--
isabelle


Le 2012-04-02 18:08, Bobblebee a écrit :
Thanks for the reply

I have tried to do this but only get #value! if I do.
I am looking at columns AJ to BN, but all rows, not just 101 [that was
just the formula I pasted here]

I have never used an array formula and don't know what I "ctrl + shift +
enter"

Thanks for trying to help.





isabelle;1600334 Wrote:


following formulas are array formulas to validate with ctrl + shift +
enter


the first lowest values address
=ADDRESS(1,SMALL(IF($AJ$101:$BN$101=SMALL($AJ$101: $BN$101,1),COLUMN($AJ$101:$BN$101)),1))

the second lowest values address
=ADDRESS(1,SMALL(IF($AJ$101:$BN$101=SMALL($AJ$101: $BN$101,2),COLUMN($AJ$101:$BN$101)),2))

the third lowest values address
=ADDRESS(1,SMALL(IF($AJ$101:$BN$101=SMALL($AJ$101: $BN$101,3),COLUMN($AJ$101:$BN$101)),3))

you can add INDIRECT function to get the value instead the address

--
isabelle



Le 2012-04-01 04:51, Bobblebee a écrit :-
Hi
first post here, hopefully someone can help.

I have a spreadsheet with a set of values attached to names, in rows.

I want to choose the 3 lowest values in each row and return the
column
headings and have set up these formulae in cells BP-BR.
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,1),$ AJ101:$BN101,0))
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,2),$ AJ101:$BN101,0))
=INDEX($AJ$1:$BN$1,,MATCH(SMALL($AJ101:$BN101,3),$ AJ101:$BN101,0))
which works fine if all values are different.
However if 2 or 3 columns have the same value all 3 cells return the
same column heading.

How can I get the formula to return the heading from the 2nd and 3rd
occurrence of minimum values?

TIA



-