VLookUp
 

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

VLookUp
 

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

VLookUp
 

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

VLookUp
 

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

VLookUp
 

=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)

VLookUp
 

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

VLookUp
 

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