The method Joel advocates is the best approach in 2003 and earlier, but
suffers from the problem that most of the time, VLOOKUP must be called
twice, first to test for N/A and again to get the data to return to the
cell. In a large workbook with lots of VLOOKUPs, this will cause a
performance hit. In Excel 2007, you can use the IFERROR function, e.g.,

=IFERROR(VLOOKUP(...),"Value If Error")

This uses only a single VLOOKUP, but works only in Excel 2007.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Finance Guru" wrote in message
...
Way to go Joel. Thankyou very much for your assistance.
Youv've made it sound so simple,and it works
"You've just lightened a heavy load!"
Kind regards
FG


"Joel" wrote:

if(ISNA(VLOOKUP(A1,sheet4!A1:c300,2,0)),"",VLOOKUP (A1,sheet4!A1:c300,2,0))

the first VLOKKUP is used to test if you get a NA. It is inside the
ISNA().
The "" is if it does find the NA then print nothing. The second VLOOKUP
returns the value when there isn't a NA.

"Finance Guru" wrote:

Hi Joel - Thanks for a FAST response.
I don't quite undestand the the reason for the second Vllookup() , or
what
should go into the quotes.

My VLOOKUP() looks like this - VLOOKUP(A1,sheet4!A1:c300,2,0). Am i
not
able to adapt this in some way,to display a blank cell wher the
criteria is
not met.

I hope you can help.
Thanks again


"Joel" wrote:

=if(isNA(Vllookup()),"",Vllookup())

"Finance Guru" wrote:

Can someone please advise me ; if VLOOKUP( ) doesn't find the
result in the
formula,the the cell shows #NA. How can I get a cell to be
blank,if the
value isn't found.

I have only just started using VLOOKUP & HLOOKUP,so any help would
be
greatfully accepted. Thanks