can we hide #N/A display because of VLOOKUP

  • Thread starter Thread starter ___Zoom
  • Start date Start date
Z

___Zoom

How can we hide displaying "#N/A" false indicator becasue of the normal
function of VLOOKUP formula in a cell without disturbing its operation?.

My formula stands like this at Sheet1!A1:
=VLOOKUP(A1;Sheet2!A:C;2;FALSE)

and when Sheet1!A1 is empty naturally I get a "#N/A" display.
I want NO error messages displayed when the cell is empty.
Can anyone comment?
TIA
 
Hi Zoom!

Try:

=IF(ISNA(VLOOKUP(A1;Sheet2!A:C;2;FALSE)),"",VLOOKUP(A1;Sheet2!A:C;2;FA
LSE))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
You can write a custom function, which calls VLookup itself with the
parameter you gave and check if it returns the "N/A".

The syntax is as follows :

Application.WorksheetFunction.VLookup(....)
 
Hi Zoom!

Same answer as over in programming.

Try:

=IF(ISNA(VLOOKUP(A1;Sheet2!A:C;2;FALSE)),"",VLOOKUP(A1;Sheet2!A:C;2;FA
LSE))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Zoom,

A number of way

=IF(ISNA(VLOOKUP(A1;Sheet2!A:C;2;FALSE)),"",VLOOKUP(A1;Sheet2!A:C;2;FALSE))

will catch #N/A

=IF(ISERROR(VLOOKUP(A1;Sheet2!A:C;2;FALSE)),"",VLOOKUP(A1;Sheet2!A:C;2;FALSE
))

will catch any error, or if you just want to test A1

=IF(A1="","",VLOOKUP(A1;Sheet2!A:C;2;FALSE))



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top