| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding ISERROR to formula
I have this formula in a cell, and correctly returns #N/A where it
does not find the data the formula looks for. Have tried ISERROR, ISNA, and ISERR to return a blank cell instead of the #N/A. Excel still doesn't like the formula. How to add a statement returning a blank? =IF(R13=1,VLOOKUP(D13,'LABR A'!$B$3:$D $327,3,FALSE),IF(R13=2,VLOOKUP(D13,'LABR B'!$B$3:$D $327,3,FALSE),IF(R13=3,VLOOKUP(D13,'LABR C'!$B$3:$D $327,3,FALSE),IF(R13=4,VLOOKUP(D13,'LABR D'!$B$3:$D $327,3,FALSE),IF(R13=5,VLOOKUP(D13,'LABR E'!$B$3:$D$327,3,FALSE)))))) This formula works well, b t w. Thanks for what I thought was a no- brainer. Pierre |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding ISERROR to formula
=IF(ISNA(YourFormula),"",YourFormula)
Vaya con Dios, Chuck, CABGx3 "Pierre" wrote: I have this formula in a cell, and correctly returns #N/A where it does not find the data the formula looks for. Have tried ISERROR, ISNA, and ISERR to return a blank cell instead of the #N/A. Excel still doesn't like the formula. How to add a statement returning a blank? =IF(R13=1,VLOOKUP(D13,'LABR A'!$B$3:$D $327,3,FALSE),IF(R13=2,VLOOKUP(D13,'LABR B'!$B$3:$D $327,3,FALSE),IF(R13=3,VLOOKUP(D13,'LABR C'!$B$3:$D $327,3,FALSE),IF(R13=4,VLOOKUP(D13,'LABR D'!$B$3:$D $327,3,FALSE),IF(R13=5,VLOOKUP(D13,'LABR E'!$B$3:$D$327,3,FALSE)))))) This formula works well, b t w. Thanks for what I thought was a no- brainer. Pierre |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding ISERROR to formula
Given the naming system you used for your sheet tabs, the formula you posted
can be reduced to this... =VLOOKUP(D13,INDIRECT("'LABR "&CHAR(R13+64)&"'!$B$3:$D$327"),3,FALSE) So, to return a blank instead of #N/A for missing data, you would do this... =IF(ISNA(VLOOKUP(D13,INDIRECT("'LABR "&CHAR(R13+64)&"'!$B$3:$D$327"),3,FALSE)),"",VLOOK UP(D13,INDIRECT("'LABR "&CHAR(R13+64)&"'!$B$3:$D$327"),3,FALSE)) If you wish to stay with your original formula, see CLR's response (which is all I really did in my second formula). Rick "Pierre" wrote in message oups.com... I have this formula in a cell, and correctly returns #N/A where it does not find the data the formula looks for. Have tried ISERROR, ISNA, and ISERR to return a blank cell instead of the #N/A. Excel still doesn't like the formula. How to add a statement returning a blank? =IF(R13=1,VLOOKUP(D13,'LABR A'!$B$3:$D $327,3,FALSE),IF(R13=2,VLOOKUP(D13,'LABR B'!$B$3:$D $327,3,FALSE),IF(R13=3,VLOOKUP(D13,'LABR C'!$B$3:$D $327,3,FALSE),IF(R13=4,VLOOKUP(D13,'LABR D'!$B$3:$D $327,3,FALSE),IF(R13=5,VLOOKUP(D13,'LABR E'!$B$3:$D$327,3,FALSE)))))) This formula works well, b t w. Thanks for what I thought was a no- brainer. Pierre |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding ISERROR to formula
On Oct 8, 12:31 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Given the naming system you used for your sheet tabs, the formula you posted can be reduced to this... =VLOOKUP(D13,INDIRECT("'LABR "&CHAR(R13+64)&"'!$B$3:$D$327"),3,FALSE) So, to return a blank instead of #N/A for missing data, you would do this... =IF(ISNA(VLOOKUP(D13,INDIRECT("'LABR "&CHAR(R13+64)&"'!$B$3:$D$327"),3,FALSE)),"",VLOOK UP(D13,INDIRECT("'LABR "&CHAR(R13+64)&"'!$B$3:$D$327"),3,FALSE)) If you wish to stay with your original formula, see CLR's response (which is all I really did in my second formula). Rick "Pierre" wrote in message oups.com... I have this formula in a cell, and correctly returns #N/A where it does not find the data the formula looks for. Have tried ISERROR, ISNA, and ISERR to return a blank cell instead of the #N/A. Excel still doesn't like the formula. How to add a statement returning a blank? =IF(R13=1,VLOOKUP(D13,'LABR A'!$B$3:$D $327,3,FALSE),IF(R13=2,VLOOKUP(D13,'LABR B'!$B$3:$D $327,3,FALSE),IF(R13=3,VLOOKUP(D13,'LABR C'!$B$3:$D $327,3,FALSE),IF(R13=4,VLOOKUP(D13,'LABR D'!$B$3:$D $327,3,FALSE),IF(R13=5,VLOOKUP(D13,'LABR E'!$B$3:$D$327,3,FALSE)))))) This formula works well, b t w. Thanks for what I thought was a no- brainer. Pierre- Hide quoted text - - Show quoted text - Rick and CLR,thanks for your help. They both worked marvelously. :) Pierre |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using iserror in formula | Excel Discussion (Misc queries) | |||
| Using ISERROR to Solve #DIV/0 in a formula | Excel Worksheet Functions | |||
| Int Iserror Len Mid Find formula | Excel Worksheet Functions | |||
| ISERROR | Excel Worksheet Functions | |||
| iserror | Excel Discussion (Misc queries) | |||
Linear Mode
