formula help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Heather C - 25 Apr 2008 19:17 GMT

I would like the cell to say something different then #N/A when it cannot
find the exact value. This is my current formula =IF(A22>0,
VLOOKUP(C22,UPC!A4:H1302,2,FALSE),"")

Reply to this Message

Kevin B - 25 Apr 2008 19:23 GMT

The generic fix is =IF(ISERROR(YourFormula),ErrVal,YourFormula)

Using your example it would look like the following:

=IF(ISERROR(IF(A22>0, VLOOKUP(C22,UPC!A4:H1302,2,FALSE),"")),0,IF(A22>0,
VLOOKUP(C22,UPC!A4:H1302,2,FALSE),""))

Hope this helps.

Signature

Kevin Backmann

> I would like the cell to say something different then #N/A when it cannot
> find the exact value. This is my current formula =IF(A22>0,
> VLOOKUP(C22,UPC!A4:H1302,2,FALSE),"")

Reply to this Message

Dave Peterson - 25 Apr 2008 19:26 GMT

=if(isna(vlookup(...)),"",if(vlookup(...)="","",vlookup(...))))

or

=if(iserror(1/len(vlookup(...)),"",vlookup(...))

And xl2007 has added an =iferror(), too.

You'll have to add the =if(a22>0, in front of the one you need!

> I would like the cell to say something different then #N/A when it cannot
> find the exact value. This is my current formula =IF(A22>0,
> VLOOKUP(C22,UPC!A4:H1302,2,FALSE),"")

Signature

Dave Peterson

Reply to this Message

RagDyer - 25 Apr 2008 19:31 GMT

Try this:

=IF(OR(A22<=0,ISNA(MATCH(C22,UPC!A4:A1302,0))),"",VLOOKUP(C22,UPC!A4:H1302,2,0))

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>I would like the cell to say something different then #N/A when it cannot
> find the exact value. This is my current formula =IF(A22>0,
> VLOOKUP(C22,UPC!A4:H1302,2,FALSE),"")

Reply to this Message