Simplest way to change what is displayed when an Excel formula results in an error

Hello,

I seem to remember a simple and shorter way to define results when adding the Excel ISERROR() function to the beginning of a formula.

Can someone tell me if that's true, and if so, remind me what it is?

At the time this issue came to mind, I was using the following formula in a setting in which I knew that many of the cells would have errors (ie the presence of the errors is not the problem):

=VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0)

Open in new window

However, the error cells currently display #N/A and my desire is simply to display a more subtle indicator for error, namely, a backtick (`) (instead of the #N/A).

I came up with the following solution, and it does the trick (ie displays ` rather than #N/A in cells with errors) but its a bulkier and more cumbersome formula than what I remember:

=IF(ISERROR(
VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0)),"`",
VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0))

Open in new window

Is there a way to simplify that?

Thanks

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 48

Wayne Taylor (webtubbs)Commented: 2016-08-17

Yep. Use IFERROR instead of ISERROR...

=IFERROR(VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0), "`")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 46

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented: 2016-08-17

What excel version you are using?
If you are using 2007 or later, you may use the IFERROR function as suggested by Wayne.

Previous questions suggest they use XL 2010. And does anyone use XL 2003 anymore?

@Wayne
Are you asking me?

LVL 30

Roy CoxGroup Finance ManagerCommented: 2016-08-17

Excel 2003 is still in use, but I don't often see examples now.

A universal solution could be to use Conditional Formatting to hide errors, read this

You can also use ISNA

= IF ( ISNA (your formula ) , " " , your formula )

Remember that errors can often be useful though, if they are hidden then the user may not be aware that an error is occurring.

WeThotUWasAToadAuthor Commented: 2016-08-18

Yep. Use IFERROR instead of ISERROR...

"Yep" Wayne, that is exactly what I was after. I didn't recall that there was an "IF" and "IS" flavor so I was looking for something else. I'll never forget again though, after seeing them side-by-side. Many thanks.

What excel version you are using?

2010

You can also use ISNA
= IF ( ISNA (your formula ) , " " , your formula )

One of the great perks of a subscription to EE: learning about things you never before knew existed. Thanks Roy

_____________________________________________________
I worry about all the things I know I don't know,
but what really concerns me
are all the things I don't know
and don't even know I don't know them.

Simplest way to change what is displayed when an Excel formula results in an error

Who is Participating?