# Thread: Wrong ISSEROR (Excell 2002)

1. ## Wrong ISSEROR (Excell 2002)

Hi

Can somebody tell me whats wrong with this formula please I have been trying to correct it for ages.

=IF(ISERROR(IF(OR(B7="",\$N\$2<>0.45))),0,IF(ISERROR (VLOOKUP(B7,Chem_Table,PT_PDPrice,FALSE)),1,0))

2. ## Re: Wrong ISSEROR (Excell 2002)

Perhaps we can help if you tell us what you want the formula to do.

3. ## Re: Wrong ISSEROR (Excell 2002)

Hi Hans

Sorry, at the moment it is as below, if B7 is blank it shows #N/A I would like it to be blank

=IF(OR(B7="",\$N\$2<>0.45),0,IF(ISERROR(VLOOKUP(B7,C hem_Table,PT_PDPrice,FALSE)),1,0))

4. ## Re: Wrong ISSEROR (Excell 2002)

That formula returns 0 if B7 is blank, not #N/A, so I still don't understand.

5. ## Re: Wrong ISSEROR (Excell 2002)

Very wide screenshot reduced in size by HansV - please keep images within 640 x 480

Hi Hans

it shows #N/A in mine see screen shot

Regards

6. ## Re: Wrong ISSEROR (Excell 2002)

Hans is right, it does not show #N/A. Then again we do not have the Vlookup table that you refer to.
You have covered for the #N/A in you IF by using the ISERROR function! So when I test it it never shows an error!

PS: if you want to return a blank anyways should the function not look something like this...
<hr>=IF(OR(B7="",\$N\$2<>0.45),"",IF(ISERROR(VLOOKUP(B7,Chem_Table,PT_PDPrice,FALSE )),1,""))<hr>

Could you sent a scale down version of your workbook if the problem persists. (If there is not confidentiality of course!)

7. ## Re: Wrong ISSEROR (Excell 2002)

The reason that N7 is #N/A is not that B7 is blank but that N2 is #N/A. Does this do what you want?

=IF(ISNA(\$N\$2),0,IF(OR(B7="",\$N\$2<>0.45),0,IF(ISER ROR(VLOOKUP(B7,Chem_Table,PT_PDPrice,FALSE)),1,0)) )

If you want a blank if N2 is #N/A, replace the first 0 in the formula by "".

8. ## Re: Wrong ISSEROR (Excell 2002)

In your VLookup, what is PT_PDP Price?
Remember, this argument must return a number. It asks for a column index number!

9. ## Re: Wrong ISSEROR (Excell 2002)

Hi Hans

That works fine, Thanks to all contributors.

10. ## Re: Wrong ISSEROR (Excell 2002)

Hi Rudi

PT_PDP is a named cell used a variable, so if I add column to the sheet I don't have to copy down all the formulas again.

Regards

11. ## Re: Wrong ISSEROR (Excell 2002)

A way around this problem of expanding lookup tables is to use a combination of index and match (see <post#= 244408>post 244408</post#>). With this scheme inserting new columns does not require the need to list the "offset" since you actually define the range. The range (lookup and output) may even be on separate sheets.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•