Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|