IS ERROR?

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

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.
 
K

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?

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...
 
J

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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup help 4
Comparing Dates 3
hiding an error value, using INDEX/MATCH 3
Sumproduct multiplying instead of adding 4
formula error 1
if function and date 2
Vacation Accruals 3
If formula Date not Blank 2

Top