IFERROR formula returning a 0 and need it to return a blank cell

[Peptide]

[TABLE="width: 392"]
<tbody>[TR]
[TD="class: xl102, width: 392"]Hello,
On the formula below, if there is no corresponding value in AZ3:BM3 it returns a 0 (zero) and I would like it to return a blank cell.

Thanks in advance!

=IFERROR(INDEX('PricingTable'!$AZ$3:$BM$3,MATCH(B87,'PricingTable'!$AZ$4:$BM$4,0)),"")[/TD]
[/TR]
[TR]
[TD="class: xl102, width: 392"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

What are you expecting as a response?

If you expect a text value, you can just add &"" to the end. But it will make numbers into text values too.

If you expect a number, you can try:

=IFERROR(1/(1/INDEX(PricingTable!$AZ$3:$BM$3,MATCH(B87,PricingTable!$AZ$4:$BM$4,0))),"")

but that will not work for text values, and it will change an actual 0 to an empty cell.

If it could be either numeric or text, or you want an actual 0 to appear, then you may have to resort to this:

=IFERROR(IF(INDEX(PricingTable!$AZ$3:$BM$3,MATCH(B87,PricingTable!$AZ$4:$BM$4,0))="","",INDEX(PricingTable!$AZ$3:$BM$3,MATCH(B87,PricingTable!$AZ$4:$BM$4,0))),"")

 

[Peptide]

Eric W,

The &"" worked great. For my own learning, what is it telling it to do?

Quesion: On my original formula, shouldn't the IFERROR function with the "" at the end return a blank if the value of the cell it should return is blank?

=IFERROR(INDEX('PricingTable'!$AZ$3:$BM$3,MATCH(B87,'PricingTable'!$AZ$4:$BM$4,0)),"")

 

[Eric W]

Well, the value returned isn't an error, it's a blank. I figured the IFERROR was there in case the B87 cell didn't match anything, that would create an error. The problem with the "" turning to a 0 is just a quirk of Excel, that turns a "" into a 0. I don't know why that happens, but the 3 formulas I showed you are the usual ways around that.

The & is the concatenation operator, so it's just saying "concatenate a null to the end of the formula." This tells Excel that the entire result is a text value, so it doesn't do the "" to 0 conversion.

 

[Peptide]

As Always Eric - thanks for the answer and insight!

Bob

 

[Peptide]

Eric - per your thought, maybe I don't need the IFERROR fucntion?

Basically the logic is if B87 is found in AZ4:BM4 then return corresponding value from AZ3:BM3, if the value is blank, then return blank. Could I alter to take out the IFERROR and still get a blank return?

=IFERROR(INDEX('PricingTable'!$AZ$3:$BM$3,MATCH(B87,'PricingTable'!$AZ$4:$BM$4,0)),"")

 

[Eric W]

If you KNOW that B87 will match something in AZ4:BM4, then you can remove the IFERROR. Maybe B87 is populated by a drop-down list generated from AZ4:BM4. But if there's a chance that B87 won't match, you may want to keep the IFERROR.

Glad to help!

 

[Peptide]

Eric - what if it does match, but the corresponding value in AZ3:BM3 is a blank cell. I would want the return value to be a blank cell. Still leave in the IFERROR?

 

[Eric W]

If you might find a blank cell, leave in the &"".

If the B87 might not find a match in AZ4:BM4, leave in the IFERROR. (Or you could leave it in anyway, it doesn't hurt anything.)

 

[Peptide]

Thx Again Eric!