CODEWITHSUNDEEP
excelexcel-2013
HollyAnne6
HollyAnne6

Reputation: 93

How do I suppress the value of #VALUE! in Excel 2013?

How do I write this into the formula to make the #VALUE! blank?

=IF(ISERROR(VLOOKUP($B10,Claims!$A$1:$G$18591,2,0)),"",VLOOKUP($B10,Claims!$A$1:$G$18591,2,0))/1000

enter image description here

Upvotes: 0

Views: 147

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

The issue is when the VLOOKUP returns an error you are trying to divide a null string by 1000.

So add the division into the check:

=IF(ISERROR(VLOOKUP($B10,Claims!$A$1:$G$18591,2,0)/1000),"",VLOOKUP($B10,Claims!$A$1:$G$18591,2,0)/1000)

But, there is no need for the use of ISERROR, wrap in IFERROR:

=IFERROR(VLOOKUP($B10,Claims!$A$1:$G$18591,2,0)/1000,"")

It avoids the need of duplicate enteries.

Upvotes: 2

Related Questions