IS ERROR?

[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))

 

[Jim Thomlinson]

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.

 

[KC]

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

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

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))

 

[Jim Thomlinson]

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

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

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

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))