Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
I'm using VLOOKUP and its working great, however, when there's nothing in the
cells the formula is looking up, the cell contains #N/A. I'd like not to see that, rather see a blank cell. Is there any way to do it? The formula I'm using is =VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
iserror will do it
=If(iserror(VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8),"",VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8) -- -John Please rate when your question is answered to help us and others know what is helpful. "Karen Smith" wrote: I'm using VLOOKUP and its working great, however, when there's nothing in the cells the formula is looking up, the cell contains #N/A. I'd like not to see that, rather see a blank cell. Is there any way to do it? The formula I'm using is =VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
Try this:
=IF(ISNA(VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)),"",VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8) -- Biff Microsoft Excel MVP "Karen Smith" wrote in message ... I'm using VLOOKUP and its working great, however, when there's nothing in the cells the formula is looking up, the cell contains #N/A. I'd like not to see that, rather see a blank cell. Is there any way to do it? The formula I'm using is =VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
Maybe one way:
=IF(ISNA(VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)),"",VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8) HTH, Paul -- "Karen Smith" wrote in message ... I'm using VLOOKUP and its working great, however, when there's nothing in the cells the formula is looking up, the cell contains #N/A. I'd like not to see that, rather see a blank cell. Is there any way to do it? The formula I'm using is =VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
It's better to use ISNA to trap N/A errors otherwise there might be another
error that gets trapped that might have been of interest to know about. -- Regards, Peo Sjoblom "John Bundy" (remove) wrote in message ... iserror will do it =If(iserror(VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8),"",VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8) -- -John Please rate when your question is answered to help us and others know what is helpful. "Karen Smith" wrote: I'm using VLOOKUP and its working great, however, when there's nothing in the cells the formula is looking up, the cell contains #N/A. I'd like not to see that, rather see a blank cell. Is there any way to do it? The formula I'm using is =VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
Excel 2007 has the IFERROR function for this purpose:
=IFERROR(value, value_if_error) Prior versions must use the ISERROR/ISNA methods described above or a user-defined function that implements IFERROR. The problem with IFERROR/ISNA is that you are executing the same functions twice. This can lead to worksheet calculation slowdowns or outright errors when you don't update both versions of the formula. To help usability/readability it may be useful to put the formula into a named range like: LookupResult = VLOOKUP(A8 & "-" & G8,'StiboRates'!$C$38:$D $53,2,0)*F8+85+(22.5*F8)+(225*13)*H8 Then in your worksheet use the following: =if(iserror(LookupResult),"",LookupResult) This method is likely slower yet due to the named ranges but now you only have to edit one copy of the formula. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
Another method would be to use a dummy range.
For instance, if your vlookup results are in column B, then in column C use the formula =if(iserror(B1),"",B1). Now hide column B. Not as elegant a solution but it is much faster than the IFERROR/ISNA methods above using the the formula twice. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A to blank
wrote in message ... Excel 2007 has the IFERROR function for this purpose: =IFERROR(value, value_if_error) Prior versions must use the ISERROR/ISNA methods described above or a user-defined function that implements IFERROR. The problem with IFERROR/ISNA is that you are executing the same functions twice. This can lead to worksheet calculation slowdowns or outright errors when you don't update both versions of the formula. To help usability/readability it may be useful to put the formula into a named range like: LookupResult = VLOOKUP(A8 & "-" & G8,'StiboRates'!$C$38:$D $53,2,0)*F8+85+(22.5*F8)+(225*13)*H8 Then in your worksheet use the following: =if(iserror(LookupResult),"",LookupResult) This method is likely slower yet due to the named ranges but now you only have to edit one copy of the formula. It's still better to use ISNA than ISERROR if the OP wants to trap the #N/A and the problem with IFERROR which is a very useful function btw is that it won't work if the workbook is to be used on computers that don't have Office 2007 which is by far the most likely scenario -- Regards, Peo Sjoblom -- Regards, Peo Sjoblom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting:highlight row based on blank or non-blank c | Excel Worksheet Functions | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
where can I down Blank Worksheets, blank stmt. of account forms | Excel Discussion (Misc queries) | |||
Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
How do I make a blank cell with a date format blank? | Excel Worksheet Functions |