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

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

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

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