Adding ISERROR to formula

[Pierre]

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

 

[Guest]

=IF(ISNA(YourFormula),"",YourFormula)

Vaya con Dios,
Chuck, CABGx3

 

[Rick Rothstein \(MVP - VB\)]

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)),"",VLOOKUP(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]

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)),"",VLOOKUP(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









- Show quoted text -

Rick and CLR,thanks for your help. They both worked marvelously.

Pierre