|
VlookUp that does not return #N/A
I use this formula:
=VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
=IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0))
Regards, KL "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
Try this
=IF(ISNA(VLOOKUP(I3,marke****ch,1,FALSE))=TRUE,"No Entry",VLOOKUP(I3,marke****ch,1,FALSE)) If you want a blank displayed, change "No Entry" to "". "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
sorry, remove the equal signs inside the formula.
KL "KL" wrote in message ... =IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0)) Regards, KL "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
Hi Barb,
The =TRUE is redundant as the expression IF(x,y,z) evaluates whether x is TRUE by default and if the z-parameter is ommitted and x-parameter is not TRUE then FALSE will be returned by default too. Regards, KL "Barb Reinhardt" wrote in message ... Try this =IF(ISNA(VLOOKUP(I3,marke****ch,1,FALSE))=TRUE,"No Entry",VLOOKUP(I3,marke****ch,1,FALSE)) If you want a blank displayed, change "No Entry" to "". "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
=ISNUMBER(MATCH(I3;INDEX(marke****ch;0;1);0))+0
or =1-ISNA(VLOOKUP(I3;marke****ch;1;0)) 1 as result means a hit, and 0 a failure. carl wrote: I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
hi, Im having exactly the same problem with my vlookups
Using your formula (without the =signs) gets rid of the #N/a but returns zero for cells which did have figures in This is the formula I used =IF(ISNA(VLOOKUP($B3,HrsMon,2,FALSE)),"",VLOOKUP($ B3,HrsMon,2,FALSE)) Can you help? Cheers "KL" wrote in message ... sorry, remove the equal signs inside the formula. KL "KL" wrote in message ... =IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0)) Regards, KL "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
Hi Jeanette,
I think there may be two explanations to it: 1) Your table has 0's or empty cells in column 2. 2) If you use time values, then, given that time values in Excel are decimals between 0 and 1, maybe you just see the rounded numbers . Try applying the Tme or General format to the result. Regards, KL "jeanette.rimmer" wrote in message ... hi, Im having exactly the same problem with my vlookups Using your formula (without the =signs) gets rid of the #N/a but returns zero for cells which did have figures in This is the formula I used =IF(ISNA(VLOOKUP($B3,HrsMon,2,FALSE)),"",VLOOKUP($ B3,HrsMon,2,FALSE)) Can you help? Cheers "KL" wrote in message ... sorry, remove the equal signs inside the formula. KL "KL" wrote in message ... =IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0)) Regards, KL "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
Thanks Karl,
I did have 0's in column 2 and hadnt copied my vlookup correctly, Cheers "KL" wrote in message ... Hi Jeanette, I think there may be two explanations to it: 1) Your table has 0's or empty cells in column 2. 2) If you use time values, then, given that time values in Excel are decimals between 0 and 1, maybe you just see the rounded numbers . Try applying the Tme or General format to the result. Regards, KL "jeanette.rimmer" wrote in message ... hi, Im having exactly the same problem with my vlookups Using your formula (without the =signs) gets rid of the #N/a but returns zero for cells which did have figures in This is the formula I used =IF(ISNA(VLOOKUP($B3,HrsMon,2,FALSE)),"",VLOOKUP($ B3,HrsMon,2,FALSE)) Can you help? Cheers "KL" wrote in message ... sorry, remove the equal signs inside the formula. KL "KL" wrote in message ... =IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0)) Regards, KL "carl" wrote in message ... I use this formula: =VLOOKUP(I3;marke****ch;1;FALSE) Can it be modified to not return #N/A ? Thank you in advance. |
| All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com