RE-write if function with isna function for more values

**funtastic** · 07-10-2012, 04:41 AM

how can i rewrite this function

=IF(ISNA(A1),IF(ISNA(A2),IF(ISNA(A3),A4,A3),A2),A1)

i have a list of values in the column A1:A20 i want to use the

the functions is :
if the value of A1 is wrong use A2, but if the value of A2 is wrong use A3, and if the value of A3 is wrong use A4 ...... and so on up to A20

**Bob Phillips** · 07-10-2012, 04:43 AM

Try this array formula

=INDEX(A1:A4,MIN(IF(NOT(ISNA(A1:A4)),ROW(A1:A4))))

**funtastic** · 07-10-2012, 06:21 AM

Dear Bob

no not success with me

this is my list
it should give me value 5

#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
5
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

**Bob Phillips** · 07-10-2012, 07:03 AM

Originally Posted by funtastic

Dear Bob

no not success with me

I'll bet you didn't array-enter it.

**Ace_XL** · 07-10-2012, 06:29 AM

In that case, replace Bob's formula with

=INDEX(A1:A20,MIN(IF(NOT(ISERROR(A1:A20)),ROW(A1:A20))))

Please confirm with Ctrl+Shift+Enter and not just Enter as this is an array formula

**funtastic** · 07-10-2012, 06:49 AM

Yessssssssssss success

**funtastic** · 07-12-2012, 04:33 AM

im a very very basc user . u can say underground excel user

RE-write if function with isna function for more values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

RE-write if function with isna function for more values

Re: RE-write if function with isna function for more values

Re: RE-write if function with isna function for more values

Re: RE-write if function with isna function for more values

Re: RE-write if function with isna function for more values

Re: RE-write if function with isna function for more values

Re: RE-write if function with isna function for more values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions