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.

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(GETPIVOTD ATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laborator y",Sheet1!I42963)="",0,GETPIVOTDATA("Cost",Cost,"A nalyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)) ,IF(VLOOKUP(A42963,LL_Cost,2,0)="",0,VLOOKUP(A4296 3,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.

--
-SA


"LAB" wrote:

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.

Oops! Ignore that last line. :)
--
-SA


"StumpedAgain" wrote:

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(GETPIVOTD ATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laborator y",Sheet1!I42963)="",0,GETPIVOTDATA("Cost",Cost,"A nalyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)) ,IF(VLOOKUP(A42963,LL_Cost,2,0)="",0,VLOOKUP(A4296 3,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.

--
-SA


"LAB" wrote:

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.

Didn't work actually. Still returning ref# error

"StumpedAgain" wrote:

Oops! Ignore that last line. :)
--
-SA


"StumpedAgain" wrote:

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(GETPIVOTD ATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laborator y",Sheet1!I42963)="",0,GETPIVOTDATA("Cost",Cost,"A nalyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)) ,IF(VLOOKUP(A42963,LL_Cost,2,0)="",0,VLOOKUP(A4296 3,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.

--
-SA


"LAB" wrote:

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.

How about this:

=IF(ISERROR(IF(ISNA(VLOOKUP(A42963,LL_Cost,2,0)),G ETPIVOTDATA("Cost",Cost,"Analyte",Sheet1!F42963,"L aboratory",Sheet1!I42963),VLOOKUP(A42963,LL_Cost,2 ,0))),0)

??

--
-SA


"LAB" wrote:

Didn't work actually. Still returning ref# error

"StumpedAgain" wrote:

Oops! Ignore that last line. :)
--
-SA


"StumpedAgain" wrote:

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(GETPIVOTD ATA("Cost",Cost,"Analyte",Sheet1!F42963,"Laborator y",Sheet1!I42963)="",0,GETPIVOTDATA("Cost",Cost,"A nalyte",Sheet1!F42963,"Laboratory",Sheet1!I42963)) ,IF(VLOOKUP(A42963,LL_Cost,2,0)="",0,VLOOKUP(A4296 3,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.

--
-SA


"LAB" wrote:

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.

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

On Jul 10, 11:35*pm, LAB wrote:
I am using a IF ISNA with two different vlookups.

=IF(ISNA(VLOOKUP(A42963,LL_Cost,2,0)),GETPIVOTDATA ("Cost",Cost,"Analyte",Sh*eet1!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.