I think this is a question about how to nest an IFERROR() command in a general IF() formula. However, it may actually be more a question about the VLOOKUP() formula. I'm not sure.

Syntax (for my own reference):
IFERROR(value, value_if_error)
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Suppose you have a spreadsheet with the following:

Some range (with 2 columns) named: Table
Column A: text entries
Column B: VLOOKUP() formula

where the Column B formula is:

=VLOOKUP(A1,Table,2,0)

The three possible scenarios are:

1) If the "lookup_value" from A1 exists in Table and the corresponding 2nd-column cell has a value,
the formula will display that value.

2) If the "lookup_value" from A1 exists in Table and the corresponding 2nd-column cell is blank,
the formula will display 0.

3) If the "lookup_value" from A1 does not exist in Table,
the formula will display #N/A.

Now, in place of the above, what formula would display a backtick ("`") for both scenarios 2 and 3?

It's possible to force a zero result from the VLOOKUP to give an error, e.g. by dividing 1 by the VLOOKUP, and then you could use this version
=IF(ISERROR(1/VLOOKUP(A1,Table,2,0)),"`",VLOOKUP(A1,Table,2,0))

To return a grave accent (aka "backtick" ... `) character if a VLOOKUP would return 0 or #N/A!, write the formula this way: =IF(ISNA(VLOOKUP(A1,Table,2,FALSE)),"`",IF(VLOOKUP(A1,Table,2,0)=0,"`",VLOOKUP(A1,Table,2,0)))

IFERROR really doesn't work in this instance since it only allows two possible results - either a correct lookup or the replacement value for the error. Since 0 is a valid result, it can't be trapped. It's just easier to use the ISNA test and nested IF statements.

That only works if zero is only ever returned as a result of a blank in the return column - if zero can be a valid result (not from a blank) and you want zero returned in that case then I would make a very small adjustment to Glenn's suggestion, i.e. check for "" rather than zero with the second IF like this:

I knew Barry would have some ingenous ways to approach this, but I do need to point out that the first formula: =IF(ISERROR(1/VLOOKUP(A1,Table,2,0)),"`",VLOOKUP(A1,Table,2,0))

is only applicable then the value to be returned from the VLOOKUP is numeric;
otherwise, the result always evaluates to "`".

Barry's second formula is spot-on and a necessary change expecially if you're dealing with numeric data and have the possibility of actual zero values in the source data.

And, dangme, that last formula blew my mind. Pretty cool.

Rory, another good solution! One minor correction on your second formula: =IFERROR(VLOOKUP(A4,Table,2,0) & "","`") (remove 2nd close paranthesis)
But as you said, that formula will not return a grave accent if there is a blank value in the lookup return column.

This demonstrates one of the neat things about both Excel and EE: there is almost always more than one method to finding a solution.

"This demonstrates one of the neat things about both Excel and EE: there is almost always more than one method to finding a solution."

0

Write Comment

By clicking you are agreeing to Experts Exchange's Terms of Use.

Featured Post

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards.
Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option
David Miller (dlmille)
Intro
Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…