How can I prevent #NA in Lookup

[Finance Guru]

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

 

[Joel]

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

 

[IanKR]

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

I wrote my own UDF:

Function NewVLookup(Value As Variant, Table As Variant, _
ColIndex As Integer, RangeLookup As Boolean) As Variant
With Application
If .IsNA(.VLookup(Value, Table, _
ColIndex, RangeLookup)) Then
NewVLookup = 0 '<================ change 0 to
"" if you want blank instead of 0
Else
NewVLookup = .VLookup(Value, Table, ColIndex, RangeLookup)
End If
End With
End Function

 

[Finance Guru]

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

 

[Finance Guru]

Hi Ian - Thanks for replying,but i think this way ahead of me,I am not a
programmer. I wouldn't even begin to know where to start.

Thanks anyway
FinanceGuru

 

[Joel]

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]

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

 

[Chip Pearson]

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]

Sorry I should have mentioned that I am on Excel 2003. I will remember to
quote the version number I am using on any future questions.
Thanks for taking the time to respond.
FG

 

[IanKR]

Hi Ian - Thanks for replying,but i think this way ahead of me,I am

not a programmer. I wouldn't even begin to know where to start.

Thanks anyway
FinanceGuru

Just copy the code I posted into a normal code module. I assumed that
because you posted this query in the ms....programming NG, you were after a
VBA solution.

 

[syoung]

I tried this formula, but it did not work for me. Instead of a blank, the
number returned was the closest number in the array (as if TRUE was used in
the vlookup). Do you have a suggestion? Here is my forumula

=IF(ISNA(VLOOKUP(W7,'[INC STMTS.xls]detail for
input'!$A$2:$C$4979,2,0)),"",VLOOKUP(W7,'[INC STMTS.xls]detail for
input'!$A$2:$C$4979,2,0))

Why is there a 0 instead of FALSE at the end of the vlookup?

Thanks for helping, Sue