Lookup an array question

[Luong]

Hello,

What Excel function do I use to lookup an array in a row,
if any value on the row is greater than 1, input that
value?

Example:
A B C D E
1 #N/A 02-15 #N/A #N/A
2 #N/A #N/A #N/A 05-07
3 01-56 #N/A #N/A #N/A
4 #N/A 02-89 #N/A #N/A
5

In cell A1, I would like for it to automatically fill in
02-15. Cell A2 to fill in 05-07, etc.

In summary, need to create a function in A1, look at cell
B1 to E1, find the cell that is not #N/A, put the value of
that cell in A1. Thanks

 

[Vasant Nanavati]

Your requirements seem contradictory, but try:

=OFFSET(A1,0,SUM((IF(NOT(ISNA(B1:E1)),COLUMN(B1:E1),0)))-1)

 

[Vasant Nanavati]

Sorry, should mention that this is an array formula <Crtl> <Shift> <Enter>.

 

[Tom Ogilvy]

In A1 put in

=INDEX($B1:$E1,1,MATCH(FALSE,ISERROR($B1:$E1),0))

enter with Ctrl+Shift+Enter rather than just enter since it is an array
formula.

then drag fill down column A.

 

[Alan Beban]

=INDEX(B1:E1,1,MATCH(TRUE,B1:E1>0,0)) array entered and copied down.

Aln Beban