| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup a Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Limitations | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Hybrid Mode
