Look across array and return non-zero values sequentially

[MM91]

Hi -

Probably best explained by way of example. Please see attached.

Many thanks in advance.
Mohammed.

[Glenn Kennedy]

use this array formula in B10, copied across and down:
=IFERROR(INDEX(3:3,SMALL(IF($B3:$M3>0,COLUMN($B3:$M3)),COLUMNS($B10:B10))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Phuocam]

Try this in B10:

=IF(COLUMNS($B10:B10)>COUNTIF($B3:$M3,">0"),"",INDEX(3:3,SMALL(IF($B3:$M3>0,COLUMN($B3:$M3),""),COLUMNS($B10:B10))))

Enter with Ctrl+Shift+Enter.

Copy cross and down.

[Tony Valko]

What version of Excel does this have to work in?

Your profile says you're using Excel 2003 but the sample file is in the *.xlsx format which requires Excel 2007 or later.