Help with Excel Function 2

[Boondy]

Hi,

I have creadted a VLOOKUP function which is working perfectly:

=VLOOKUP(A53,Report!$A$2:$B$200,2,FALSE)

However when it does not find a match it returns the value #N/A which is normal. I would like to change it so that it does not return a value at all, or returns a value i specify. Can this be done? I was thinking of creating an IF statement to fix this, but i dont know if you can have multiple formulas (VLOOKUP & IF) within the same cell...

Can anyone help?

 

[fredericofonseca]

This should do the trick

=if(iserror(VLOOKUP(A53,Report!$A$2:$B$200,2,FALSE)),"value if false",VLOOKUP(A53,Report!$A$2:$B$200,2,FALSE))




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Boondy]

Thanks for that.

About 30 seconds after submitting this thread i stumbled on the answer by accident. (Always the way isn't it?)

Thanks for your help

Emad

 

[xlbo]

Just as a little point here - if you are using a vlookup and want to trap the #N/A error, you should really use the ISNA error trapper rather than the ISERROR one as you can get other errors with vlookups (#REF! for when the lookup is outside of the lookup range for instance)

=if(ISNA(VLOOKUP(A53,Report!$A$2:$B$200,2,FALSE)),"value if false",VLOOKUP(A53,Report!$A$2:$B$200,2,FALSE))

ISERROR will still work but being as ISNA is available, you may as well use it

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244