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

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.

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



-