I need help with a IF(ISNA with vlookup

[LAB]

I am using a IF ISNA with two different vlookups.

=IF(ISNA(VLOOKUP(A42963,LL_Cost,2,0)),GETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laboratory",Sheet1!I42963),VLOOKUP(A42963,LL_Cost,2,0))

This formula works great, except that the cells that don't find anything in
either table return a #REF and I want this value to be zero. Is there a way
to do this.

 

[StumpedAgain]

I think the only way to do that is to do something like the following (or a
variation of the following):

=IF(ISNA(VLOOKUP(A42963,LL_Cost,2,0)),IF(GETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)="",0,GETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)),IF(VLOOKUP(A42963,LL_Cost,2,0)="",0,VLOOKUP(A42963,LL_Cost,2,0)))

Hopefully it fits into a single cell. I think it should (or it did on
mine).

You might have to do a Find/Replace after if this is a report.

 

[StumpedAgain]

Oops! Ignore that last line.
--
-SA

I think the only way to do that is to do something like the following (or a
variation of the following):

=IF(ISNA(VLOOKUP(A42963,LL_Cost,2,0)),IF(GETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)="",0,GETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)),IF(VLOOKUP(A42963,LL_Cost,2,0)="",0,VLOOKUP(A42963,LL_Cost,2,0)))

Hopefully it fits into a single cell. I think it should (or it did on
mine).

You might have to do a Find/Replace after if this is a report.

 

[LAB]

Didn't work actually. Still returning ref# error

Oops! Ignore that last line.

 

[StumpedAgain]

How about this:

=IF(ISERROR(IF(ISNA(VLOOKUP(A42963,LL_Cost,2,0)),GETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laboratory",Sheet1!I42963),VLOOKUP(A42963,LL_Cost,2,0))),0)

??

 

[Pete_UK]

Try it this way:

=IF(ISNA(VLOOKUP(A42963,LL_Cost,
2,0)),IF(ISERROR(GETPIVOTDATA("Cost",Cost,"Analyte",Sh­eet1!
F42963,"Laboratory",Sheet1!I42963)),
0,GETPIVOTDATA("Cost",Cost,"Analyte",Sh­eet1!
F42963,"Laboratory",Sheet1!I42963)),VLOOKUP(A42963,LL_Cost,2,0))

Hope this helps.

Pete