error.type command problem in Excel 97

[John Gilchrist]

Please notify me if I am posting to the wrong group.
REF: I am using Excel 97.

I am using ERROR.TYPE command to detect a failed VLOOKUP command.

In cell J7, I place the following VLOOKUP command:
=VLOOKUP($F$9,QB_Rpt!$B$1:$G$100,4,FALSE)

In another cell (A1) , I place the following command:
=IF(ERROR.TYPE($J$7)=7,0,$J$7)

If the VLOOKUP fails, cell J7 = #N/A (which I believe is error type 7), and
cell A1 displays $0.00 as desired.

However, if the VLOOKUP is successful, cell J7 = $90.00 (the lookup value),
but cell A1 displays #N/A, rather than the desired value of $90.00.


This is the same command which is used in the ERROR.TYPE help notes as an
example, and I cannot understand why it does not behave like the example.

I would appreciate any ideas.

Thank you
John G.

 

[Dave Peterson]

Instead of error.type, how about just:

=if(isna($j$7),.....)

I think it's the error.type itself that's returning the #n/a.
(If you do =error.type() and that cell doesn't have an error, then error.type
returns the error.)

 

[John Gilchrist]

Dave,
I tried your suggestion, and it worked fine. I see in the help notes that
there is a whole family of testing functions which should be very valuable.
Thanks again for your help.

I'm still puzzled why Microsoft would use an example which does not work -
very confusing

Thanks again,
John