So close and yet so far (IF ISNA question)

R

randomjohn

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
 
C

Chad

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
 
B

Bob Phillips

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

Bob Phillips

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 said:
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 said:
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
Click to expand...
to
get
Click to expand...
Click to expand...
 
Top