Wrong ISSEROR (Excell 2002)

[braddy60]

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))

Thanks in advance

Braddy

[HansV]

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

[braddy60]

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))

Braddy

Thanks for your response

[HansV]

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

[braddy60]

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

Braddy

[Rudi]

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!)

[HansV]

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 "".

[Rudi]

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

[braddy60]

Hi Hans

That works fine, Thanks to all contributors.

Braddy

[braddy60]

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

Braddy

[sdckapr]

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