1. ISNA formula (Excel 2002)

=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.

2. Re: ISNA formula (Excel 2002)

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))

3. Re: ISNA formula (Excel 2002)

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)

4. Re: ISNA formula (Excel 2002)

The idea should be the same:

=IF(ISNA(LOOKUP(IDate,Tran_Date,Jumbo_ND)),"",LOOK UP(IDate,Tran_Date,Jumbo_ND))

5. Re: ISNA formula (Excel 2002)

Mucho thanks!!!

6. Re: ISNA formula (Excel 2002)

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.