So close and yet so far (IF ISNA question)
 

I'm using the following formula

=INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0))

This generally works, but occasionally it doesn't find a match on page QB6,
in which case I get the #N/A! error. Instead of getting that error, I would
like to force a value into that cell (a defined number).

I'm trying to tweak it using the IF ISNA commands but just can't seem to get
it right. Thanks in advance

Try this:

=IF(ISERROR(INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0 )))=TRUE,"THERE IS AN
ERROR",INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0)))

You can replace the "There is an error" text with the forced value if you
like. Hope this helps!

-Chad

"randomjohn" wrote:

I'm using the following formula

=INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0))

This generally works, but occasionally it doesn't find a match on page QB6,
in which case I get the #N/A! error. Instead of getting that error, I would
like to force a value into that cell (a defined number).

I'm trying to tweak it using the IF ISNA commands but just can't seem to get
it right. Thanks in advance

I would just use

=IF(ISNA(INDEX([QB6]QB6!$A:$A,MATCH($B31,[QB6]QB6!$B:$B,0))),the_number,INDE
X([QB6]QB6!$A:$A,MATCH($B31,[QB6]QB6!$B:$B,0)))

--
HTH

Bob Phillips

"Chad" wrote in message
...
Try this:

=IF(ISERROR(INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0 )))=TRUE,"THERE IS AN
ERROR",INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0)))

You can replace the "There is an error" text with the forced value if you
like. Hope this helps!

-Chad

"randomjohn" wrote:

I'm using the following formula

=INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0))

This generally works, but occasionally it doesn't find a match on page
QB6,
in which case I get the #N/A! error. Instead of getting that error, I
would
like to force a value into that cell (a defined number).

I'm trying to tweak it using the IF ISNA commands but just can't seem to
get
it right. Thanks in advance

Something odd going on, it keeps inserting stuff into this formula.

Try again

=IF(ISNA(INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0))) ,the_number,INDEX(QB6!$A:$
A,MATCH($B31,QB6!$B:$B,0)))

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
I would just use


=IF(ISNA(INDEX([QB6]QB6!$A:$A,MATCH($B31,[QB6]QB6!$B:$B,0))),the_number,INDE
X([QB6]QB6!$A:$A,MATCH($B31,[QB6]QB6!$B:$B,0)))

--
HTH

Bob Phillips

"Chad" wrote in message
...
Try this:

=IF(ISERROR(INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0 )))=TRUE,"THERE IS AN
ERROR",INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0)))

You can replace the "There is an error" text with the forced value if
you
like. Hope this helps!

-Chad

"randomjohn" wrote:

I'm using the following formula

=INDEX(QB6!$A:$A,MATCH($B31,QB6!$B:$B,0))

This generally works, but occasionally it doesn't find a match on page
QB6,
in which case I get the #N/A! error. Instead of getting that error, I
would
like to force a value into that cell (a defined number).

I'm trying to tweak it using the IF ISNA commands but just can't seem
to
get
it right. Thanks in advance