Get rid of #VALUE error message

[SimonHughes]

I have a simple formula =IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),F42*0.8) which I am using to create a price list.

However, there are blank rows which creates the error #VALUE. What do I need to add into this formula to prevent the error message appearing?

I am using Excel 2010 on Win 7

 

[Aladin Akyurek]

Try...

=IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),N(F42)*0.8)

 

[SimonHughes]

Hi Aladin, that gets rid of the value error but it does drop a zero in. Can it be worked to show a blank cell? I should have been more specific in my original question

 

[Aladin Akyurek]

Hi Aladin, that gets rid of the value error but it does drop a zero in. Can it be worked to show a blank cell? I should have been more specific in my original question

You could custom format the formula cell as:

[=0]"";General

Otherwise:

Either...

=IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),IFERROR(F42*0.8,""))

Or...

=IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),IF(ISNUMBER(F42),F42*0.8,""))

 

[SimonHughes]

That works perfectly, many thanks

 

[Aladin Akyurek]

That works perfectly, many thanks

You are welcome. Thanks for providing feedback.