Hi all
I have created a VLookUp which works fine, however, because I want the data
returned to be exact and not return the closest figure, I have entered
'false' at the end of the formula. It works as expected, however, when it
cannot find an exact match it obviously returns 'N/A'.
How can I get it to return '0' instead, to make my report look better?
Thank you
Louise
Wrap your VLOOKUP formula in an IF statement, as
=IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
Vaya con Dios,
Chuck, CABGx3
"Louise" wrote:
> Hi all
>
> I have created a VLookUp which works fine, however, because I want the data
> returned to be exact and not return the closest figure, I have entered
> 'false' at the end of the formula. It works as expected, however, when it
> cannot find an exact match it obviously returns 'N/A'.
>
> How can I get it to return '0' instead, to make my report look better?
>
> Thank you
>
> Louise
Sorry to 'sound' so stupid, does this mean I enter my formula twice??
THanks
Louise
"CLR" wrote:
> Wrap your VLOOKUP formula in an IF statement, as
>
> =IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Louise" wrote:
>
> > Hi all
> >
> > I have created a VLookUp which works fine, however, because I want the data
> > returned to be exact and not return the closest figure, I have entered
> > 'false' at the end of the formula. It works as expected, however, when it
> > cannot find an exact match it obviously returns 'N/A'.
> >
> > How can I get it to return '0' instead, to make my report look better?
> >
> > Thank you
> >
> > Louise
Wrap your VLOOKUP formula in an IF statement, as
=IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
Vaya con Dios,
Chuck, CABGx3
"Louise" wrote:
> Hi all
>
> I have created a VLookUp which works fine, however, because I want the data
> returned to be exact and not return the closest figure, I have entered
> 'false' at the end of the formula. It works as expected, however, when it
> cannot find an exact match it obviously returns 'N/A'.
>
> How can I get it to return '0' instead, to make my report look better?
>
> Thank you
>
> Louise
Sorry to 'sound' so stupid, does this mean I enter my formula twice??
THanks
Louise
"CLR" wrote:
> Wrap your VLOOKUP formula in an IF statement, as
>
> =IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Louise" wrote:
>
> > Hi all
> >
> > I have created a VLookUp which works fine, however, because I want the data
> > returned to be exact and not return the closest figure, I have entered
> > 'false' at the end of the formula. It works as expected, however, when it
> > cannot find an exact match it obviously returns 'N/A'.
> >
> > How can I get it to return '0' instead, to make my report look better?
> >
> > Thank you
> >
> > Louise
Exactly, enter everything except the equal signs from your formula, like this.
=IF(ISNA(VLOOKUP(A1,B3:C68,2,FALSE)),0,VLOOKUP(A1,B3:C68,2,FALSE))
I've substituted a fake VLOOKUP here, but this is the idea.......
Vaya con Dios,
Chuck, CABGx3
"Louise" wrote:
> Sorry to 'sound' so stupid, does this mean I enter my formula twice??
> THanks
> Louise
>
> "CLR" wrote:
>
> > Wrap your VLOOKUP formula in an IF statement, as
> >
> > =IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Louise" wrote:
> >
> > > Hi all
> > >
> > > I have created a VLookUp which works fine, however, because I want the data
> > > returned to be exact and not return the closest figure, I have entered
> > > 'false' at the end of the formula. It works as expected, however, when it
> > > cannot find an exact match it obviously returns 'N/A'.
> > >
> > > How can I get it to return '0' instead, to make my report look better?
> > >
> > > Thank you
> > >
> > > Louise
=IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
yes, you would put your formula in twice:
the "if" statement works like this
If (COMPARISON) ,(if its TRUE do this), (if its FALSE do this)
you could do =if (your lookup = 0, "", your lookup)
That worked a treat, never used it before.
Thank you.
"CLR" wrote:
> Exactly, enter everything except the equal signs from your formula, like this.
>
> =IF(ISNA(VLOOKUP(A1,B3:C68,2,FALSE)),0,VLOOKUP(A1,B3:C68,2,FALSE))
>
> I've substituted a fake VLOOKUP here, but this is the idea.......
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Louise" wrote:
>
> > Sorry to 'sound' so stupid, does this mean I enter my formula twice??
> > THanks
> > Louise
> >
> > "CLR" wrote:
> >
> > > Wrap your VLOOKUP formula in an IF statement, as
> > >
> > > =IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > > "Louise" wrote:
> > >
> > > > Hi all
> > > >
> > > > I have created a VLookUp which works fine, however, because I want the data
> > > > returned to be exact and not return the closest figure, I have entered
> > > > 'false' at the end of the formula. It works as expected, however, when it
> > > > cannot find an exact match it obviously returns 'N/A'.
> > > >
> > > > How can I get it to return '0' instead, to make my report look better?
> > > >
> > > > Thank you
> > > >
> > > > Louise
You're welcome..........thanks for the feedback.
Vaya con Dios,
Chuck, CABGx3
"Louise" wrote:
> That worked a treat, never used it before.
>
> Thank you.
>
> "CLR" wrote:
>
> > Exactly, enter everything except the equal signs from your formula, like this.
> >
> > =IF(ISNA(VLOOKUP(A1,B3:C68,2,FALSE)),0,VLOOKUP(A1,B3:C68,2,FALSE))
> >
> > I've substituted a fake VLOOKUP here, but this is the idea.......
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Louise" wrote:
> >
> > > Sorry to 'sound' so stupid, does this mean I enter my formula twice??
> > > THanks
> > > Louise
> > >
> > > "CLR" wrote:
> > >
> > > > Wrap your VLOOKUP formula in an IF statement, as
> > > >
> > > > =IF(ISNA(YourVlookupFormula),0,YourVlookupFormula)
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > >
> > > > "Louise" wrote:
> > > >
> > > > > Hi all
> > > > >
> > > > > I have created a VLookUp which works fine, however, because I want the data
> > > > > returned to be exact and not return the closest figure, I have entered
> > > > > 'false' at the end of the formula. It works as expected, however, when it
> > > > > cannot find an exact match it obviously returns 'N/A'.
> > > > >
> > > > > How can I get it to return '0' instead, to make my report look better?
> > > > >
> > > > > Thank you
> > > > >
> > > > > Louise
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks