ERROR.TYPE Usage

[Nomas]

Hi All,

Trying to learn how to use the ERROR.TYPE function. I have the base function looking up named ranges to return a value (this function is in cell AW3)

=(VLOOKUP($X3,INDIRECT($AT3),$AU3,FALSE))

I would like to return the correct values in column AW but have all the error codes return an "Not Rated" if possible. I have tried using the ERROR.TYPE function by itself and with a CHOOSE function but am getting #N/A for all. I apprecaite any guidance.

Thanks

 

[Jonmo1]

Welcome to the board...

Try IF(ISNA...

=IF(ISNA(VLOOKUP($X3,INDIRECT($AT3),$AU3,FALSE)),"Not Rated",VLOOKUP($X3,INDIRECT($AT3),$AU3,FALSE))

Hope that helps..

 

[Nomas]

OK took care of some of the issues, I also get #REF, which is why I was trying the ERROR.TYPE hoping it was a catchall for errors to return a specific response. Any ideas on how to deal with the #REF also?

Thanks for the quick response, its greatly appreciated.

 

[Jonmo1]

ISNA finds specifically #N/A errors...

Change ISNA to ISERROR to trap ALL errors.

 

[Nomas]

Quite Simply, U are AWESOME! Thx.

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[T. Valko]

Try IF(ISNA...

=IF(ISNA(VLOOKUP($X3,INDIRECT($AT3),$AU3,FALSE)),"Not Rated",VLOOKUP($X3,INDIRECT($AT3),$AU3,FALSE))

And if you're using Excel 2007 or later you can reduce that to:

=IFERROR(VLOOKUP($X3,INDIRECT($AT3),$AU3,0),"Not Rated")

 

[Nomas]

Thanks T.Valko always looking to make the code simpler. I use both so will keep this in mind for 07.

 

[T. Valko]

Thanks T.Valko always looking to make the code simpler. I use both so will keep this in mind for 07.

Good deal. Thanks for the feedback!