IS ERROR?

  • Thread starter Thread starter KC
  • Start date Start date
K

KC

I'm using the following formula in date format. I need to amend the formula
so that if the result is a blank cell, the formula returns an answer of
"null" instead of 1/1/1900. I tried IF(ISERROR but it didn't work for me.


=INDEX(Hiring'!$T$4:$T$400,MATCH(B108,]Hiring'!$E$4:$E$400,0))
 
Try this...

=if(isna(MATCH(B108,'Hiring'!$E$4:$E$400,0), "",
INDEX('Hiring'!$T$4:$T$400,MATCH(B108,'Hiring'!$E$4:$E$400,0))

ISNA is far preferable to iserror. Is error ignores all error including
#Ref. If your reference gets currupted you will never know it with iserror.
It will just look like a match was not found. If you have ever tried to debug
something like that you know how difficult it is. Any error handling you do
in a formula should be as specific as possible.
 
Thanks for your response Jim, the formula is giving me an error, that
highlights the quotes where you have ,"",. Any idea how to fix?

Jim Thomlinson said:
Try this...

=if(isna(MATCH(B108,'Hiring'!$E$4:$E$400,0), "",
INDEX('Hiring'!$T$4:$T$400,MATCH(B108,'Hiring'!$E$4:$E$400,0))

ISNA is far preferable to iserror. Is error ignores all error including
#Ref. If your reference gets currupted you will never know it with iserror.
It will just look like a match was not found. If you have ever tried to debug
something like that you know how difficult it is. Any error handling you do
in a formula should be as specific as possible.
--
HTH...

Jim Thomlinson


KC said:
I'm using the following formula in date format. I need to amend the formula
so that if the result is a blank cell, the formula returns an answer of
"null" instead of 1/1/1900. I tried IF(ISERROR but it didn't work for me.


=INDEX(Hiring'!$T$4:$T$400,MATCH(B108,]Hiring'!$E$4:$E$400,0))
Click to expand...
Click to expand...
 
missing a bracket or 2...

=if(isna(MATCH(B108,'Hiring'!$E$4:$E$400,0)), "",
INDEX('Hiring'!$T$4:$T$400,MATCH(B108,'Hiring'!$E$4:$E$400,0)))

--
HTH...

Jim Thomlinson


KC said:
Thanks for your response Jim, the formula is giving me an error, that
highlights the quotes where you have ,"",. Any idea how to fix?

Jim Thomlinson said:
Try this...

=if(isna(MATCH(B108,'Hiring'!$E$4:$E$400,0), "",
INDEX('Hiring'!$T$4:$T$400,MATCH(B108,'Hiring'!$E$4:$E$400,0))

ISNA is far preferable to iserror. Is error ignores all error including
#Ref. If your reference gets currupted you will never know it with iserror.
It will just look like a match was not found. If you have ever tried to debug
something like that you know how difficult it is. Any error handling you do
in a formula should be as specific as possible.
--
HTH...

Jim Thomlinson


KC said:
I'm using the following formula in date format. I need to amend the formula
so that if the result is a blank cell, the formula returns an answer of
"null" instead of 1/1/1900. I tried IF(ISERROR but it didn't work for me.


=INDEX(Hiring'!$T$4:$T$400,MATCH(B108,]Hiring'!$E$4:$E$400,0))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top