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

=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

> 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

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

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

"IanKR" 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
>
> 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
>
>
>

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

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

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" <(E-Mail Removed)> wrote in message
news:BBFAD265-821C-4DCE-A441-(E-Mail Removed)...
> 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

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
"Chip Pearson" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:BBFAD265-821C-4DCE-A441-(E-Mail Removed)...
> > 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
>

> 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.