IFERROR with ""&VLOOKUP returning nothing

[RLCornish]

Hello, hello. This is more a question of why does this work. I LOVE the fact it does but I've chatted with several Excel folks and no one in my personal circle can tell me why this works. I'm doing some VBA work for a colleague and I noticed she has a formula that reads:

=IFERROR(""""&VLOOKUP($B16,tbl_Status,2,0),"""")

I get the the whole piece about it returning the value from column 2 of tbl_Status if it finds B16 in tbl_Status. However, what it actually does if it doesn't find a match to B16 is what is fascinating to me. If it doesn't find it based on the end of the IFERROR I would expect a blank, but without the first part, """"&, it doesn't return a blank, it returns a 0. I don't know if this is relevant but the B16 this is referring to is a value in a pivot table.

Why does adding the """"& to this return a blank instead of a 0?

Curious minds want to know.

 

[live_excel]

=IFERROR(VLOOKUP($B16,tbl_Status,2,0),"-")

Try this

 

[RLCornish]

I will say I got super happy with the "'s when I was typing the formula above. It's only "" before the & and at the end, not """". Sorry about that! The formula actually is...

=IFERROR(""&VLOOKUP($B16,tbl_Status,2,0),"")

 

[RLCornish]

Hi live_excel. Actually the formula works exactly as I want it to just the way it is. I was just trying to understand why with the "" before the & and at the end it brought back blanks instead of 0's. If I take the first part out, the ""&, it will return a value of 0. It even does that if I try the version you suggested, which thank you for that. I'm just baffled why the ""& makes it return a blank.

 

[StephenCrump]

However, what it actually does if it doesn't find a match to B16 is what is fascinating to me. If it doesn't find it based on the end of the IFERROR I would expect a blank, but without the first part, """"&, it doesn't return a blank, it returns a 0.

I think you've got it around the wrong way ...

If the formula doesn't find a match, it will return a blank, i.e. the value_if_error you've specified, shown in red below:

=IFERROR(""&VLOOKUP($B16,tbl_status,2,0),"")

If the formula does find a match, but the lookup value in column 2 of tbl_status is blank, Excel will show a zero. Concatenating a leading "" to the lookup value coerces it to a string, so blank will display as blank and not zero.

A potential downside of this approach is that if column 2 of table_status contains the numeric value 123, say, the formula will return the string value "123", which may not be what you want. If you want to return string as string, numeric as numeric, and blank as blank (not zero) you'll need a slightly more complicated formula. Here's one way you could do it:

=IFERROR(IF(ISBLANK(VLOOKUP($B16,tbl_status,2,0)),"",VLOOKUP($B16,tbl_status,2,0)),"")

 

[RLCornish]

Concatenating a leading "" to the lookup value coerces it to a string, so blank will display as blank and not zero.

Hi StephenCrump,

You are right, I had that backwards in what I wrote, BUT you did answer my question & I appreciate that very much!

 

[FDibbins]

Why are you even including "" with the vlookup?

 

[RLCornish]

Because we don't want a 0, but instead a blank. It works well, I just didn't understand that using the "" concatenate would force it to string rather than number. Like I mentioned, someone else wrote the formula, I was just trying to understand workings.

Thanks for the question.