This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Hello,

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 seems like it should be something like this:

*but I know that the IFERROR() part of the above is wrong*.

**What is the proper way to write it?**

Thanks

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.

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,

It seems like it should be something like this:

```
=
IF(VLOOKUP(A1,Table,2,0)<>"",VLOOKUP(A1,Table,2,0),
IF(VLOOKUP(A1,Table,2,0)=0,"`",
IFERROR(VLOOKUP(A1,Table,2,0),"`")))
```

Thanks

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.

Regards,

-Glenn

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

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

-Glenn

Yes, you're right, my mistake - I suppose the first formula can be disregarded - as it only works for numeric data you can use the third one instead

regards, barry

=IFERROR(IF(VLOOKUP(A1,Tab

If the returned values will always be text (other than blank cells), you can shorten it to:

=IFERROR(VLOOKUP(A1,Table,

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.

-Glenn

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=IF(ISERROR(1/VLOOKUP(A1,T

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:

=IF(ISNA(VLOOKUP(A1,Table,

That may return zero if that's the actual value in column 2

......or if you never want a zero result and the other VLOOKUP results are

numericyou could use this:=IFERROR(1/(1/VLOOKUP(A1,T

Note: this might not be the sort of formula you want to use in a workbook that others need to use and understand, due to it's lack of transparency

regards, barry