Pulling nth non zero value in a row?

[spd87]

I have a row of values and I need to pull the second non-zero value on the same sheet, which in the example below is 500:

A B C D E F G H
Index/Match(200) NEWFORMULAHERE 0 0 0 200 0 500 300 0

I use an Index/Match formula to grab the first non-zero value (200). However next to my index/match formula for the first non-zero, I need to pull the next non zero.

How can I write a formula to grab the 2nd non zero, and the 3rd non zero, etc. I'm not well versed with array formulas, so I may need some interpretation/explanation of any formulas posted.

I originally found this post: http://www.excelforum.com/excel-form...-in-a-row.html but I could not get the formula provided to work.

Can anyone help? Thanks for your time.

[martindwilson]

you could try
=IFERROR(INDEX($B$1:$T$1,SMALL((INDEX(($B$1:$T$1<>0)*(COLUMN($B$1:$T$1)-1),0)),SUMPRODUCT(--($B$1:$T$1=0))+COLUMNS($A$1:A1))),"") filled across or
=IFERROR(INDEX($B$1:$T$1,SMALL((INDEX(($B$1:$T$1<>0)*(COLUMN($B$1:$T$1)-1),0)),SUMPRODUCT(--($B$1:$T$1=0))+rows($A$1:A1))),"") filled down

[spd87]

Using the below formula is returning me a "0". Do you see anything wrong with it? I did hit CTRL+Shift+Enter. I've tried to use both formulas, but I'm not getting the 500 pulled in.

=IFERROR(INDEX($B$1:$Z$1,SMALL((INDEX(($B$1:$Z$1<>0)*(COLUMN($B$1:$Z$1)-1),0)),SUMPRODUCT(--($B$1:$Z$1=0))+COLUMNS($A$1:A1))),"")

Thank you

[martindwilson]

works for me

[spd87]

Martin,

I'm having trouble putting mine into your format, please see attached. Thanks for all of your help. spd87 v2.xlsx

[martindwilson]

there you go i changed it slightly

[spd87]

Thanks Martin, this is perfect.

[ExcelFan117]

Many thanks martindwilson! After trawling through numerous other sites with complex solutions, having your attachment helped me. Really, thank you!