in MS excel find first non n/a value in row

Question

I have dates (Jan-2014 in cell A1, Feb-2015 in cell B1 onwards) in Columns in MS Excel. I am using Hlookup to populate revenue data for those dates in row 2. When using Hlookup, I am getting some #n/a due to missing values for some dates. I need an excel formula so that for those #N/As, it picks up the first available value i.e. if there is #N/A in Cell A2 and B2 and 15 in C2 then I need an excel formula to so that 15 appears in A2 and B2 as well

A1 B1  C1 D1
N/A N/A 15 16

I tried using

=IFERROR(HLOOKUP(A1,Revenue!$C$4:$JH$306,2,0),INDEX($A2:$DI2,MATCH(1,IF(ISNA($A2:$DI2),0,1),0)))

But this didnt work. Also, this is array I would preferably like without array.

Answer 1

With data in A1 through L1, use:

=INDEX(A1:L1,MATCH(TRUE,INDEX(A1:L1<>0,),0))