ISNA formula (Excel 2002)

Topic

New Reply

=if(isna(VLOOKUP(B3,Employers!$A$1:$E$600,2,0),””,vlookup(b3,Employers!$a$1:$e$600)

This formula should stop the #NA appearing in the cells, I believe. I have used the =if(ISNA…… with indexes and matches without problem. However when I enter this particular formula I get an error message which highlights the “”, indicating that I have entered them incorrectly. I tried a space between the “” and get the same error message. What am I doing wrong, or will the =if(ISNA….. not work in conjunction with a Vlookup? Thanks for any help.

Reply | Quote

Replies

There is a closing bracket missing after both VLOOKUP parts, and you omitted the 3rd and 4th argument to the second VLOOKUP part. Here is the corrected formula:

=IF(ISNA(VLOOKUP(B3,Employers!$A$1:$E$600,2,0)),””,VLOOKUP(B3,Employers!$A$1:$E$600,2,0))

Reply | Quote

Hans,

Is it possible to do the same thing without using VLOOKUPs? I am having problems adding to this formula:

=LOOKUP(IDate,Tran_Date,Jumbo_ND)

Reply | Quote

The idea should be the same:

=IF(ISNA(LOOKUP(IDate,Tran_Date,Jumbo_ND)),””,LOOKUP(IDate,Tran_Date,Jumbo_ND))

Reply | Quote

Mucho thanks!!!

Reply | Quote

Hans,

There is no need for ISNA. If invoking a LOOKUP formula is justified, that is, Tran_Date in this paricular case in ascending order, there will be just one situation in which #N/A will obtain. That is when IDate < INDEX(Tran_Date,1). Since LOOKUP is very fast, it's a pity to slow down its speed with a scheme that requires "computing twice".

If LOOKUP is indeed justified, the following options would be better:

1)

=IF(IDate<INDEX(Tran_Date,1),"",LOOKUP(IDate,Tran_Date,Jumbo_ND))

2} Add the following at the beginning of Tran_Date:

-9.99999999999999E+307

and at the beginning of Jumbo_ND

=""

and just invoke:

=LOOKUP(IDate,Tran_Date,Jumbo_ND)

unmodified.

The last option is the best.

Reply | Quote

Thanks, Aladin, that makes sense.

Reply | Quote