I need help with a IF(ISNA with vlookup

L

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

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

StumpedAgain

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


StumpedAgain said:
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.
Click to expand...
 
S

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)

??
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Part of VLOOKUP not working 4
IF, ISNA & VLOOKUP 5
=IF(NOT(ISNA(VLOOKUP Function 2
isna vlookup #n/a but not false 1
Help: vlookup from multiple sheets and spreadhseets 1
VLOOKUP question utilizing the right and left of a given column ineed the mid too dont know how to i 1
Vlookup with AND, OR IF statements 1
Can I use an IS function with Vlookup 2

Top