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?
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.
-Glenn
0
Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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
Featured Post
Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…