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

Your requirements seem contradictory, but try:

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

--

Vasant

"Luong" wrote in message
...
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

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

--

Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Your requirements seem contradictory, but try:

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

--

Vasant

"Luong" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"Luong" wrote in message
...
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

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

Aln Beban

Luong wrote:

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