ISNA Lookup Problem

Posted by Tom in DaBurg on January 18, 2002 6:23 AM

I have this much working properly

=IF(U3="yes",0.35,VLOOKUP(F3,$R$3:$S$11,2))

But if there is no value returned I get #NA

I have tried to write a function using =if(isna(

but then it either states there is a error and highlights my .35 or it just wont work. anyhelp would be appreciated.

Tom

Posted by Aladin Akyurek on January 18, 2002 6:36 AM

Tom --

I wished you have answered a few questions I posed wrt your earlier post. If this formula indeed partially works, my hunch for the complete formula would be:

=IF(U3="Yes",0.35,IF(ISNUMBER(MATCH(F3,$R$3:$R$11,2)),VLOOKUP(F3,$R$3:$S$11,2),0))

Note that I made the VLOOKUP part return 0, if it get a chance and fails.

Aladin

========

Posted by IML on January 18, 2002 6:37 AM

If you want an exact match, try
=IF(U3="yes",0.35,IF(COUNTIF(R3:R11,F3),VLOOKUP(F3,$R$3:$S$11,2,0),"no match"))

The N/a your getting now

You are ommitting the fourth argument, leaving excel to find the closest match.
If this what you want, your list must be sorted, but N/A will still be returned as stated in the following from the help file:

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

good luck

Posted by Tom in DaBurg on January 18, 2002 8:35 AM

You know what, that worked, Now (More)

I just need to figure out how to use the
isnumber and match commands properly for myself so I don't run into this again.

Your great Aladin