#n/a

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rbell - 09 Jan 2006 11:18 GMT

I am using a formula to look up a manufactures price for parts from
list =INDEX(Sheet2!G:G,MATCH(A3,Sheet2!A:A+0)). Sheet1 is my list o
pn's, Sheets 2,3,4 etc are the mfgs. list for various product lines
This formula returns #N/A for some as they are not all on every list.
would like to either have the formula return a 0 or leave the cell blan
so I can total these or do other calculations but the #N/A's caus
errors.
I've tried some things but haven't got it right yet, any ideas?
BTW Sheet 2 col G is the mfg's cost and is in the same col in all ther
lists. Col A is the pn's in both

--
rbel

Reply to this Message

Pete_UK - 09 Jan 2006 11:46 GMT

Basically, you want to intercept the error and return 0 or "", as
follows:

=IF(ISNA(your_formula), 0 ,your_formula) , or
=IF(ISERROR(your_formula), 0 ,your_formula)

The first version will only trap the #N/A error (if there is no match),
the second one will trap all errors - a zero will be returned. Change
to "" if you want a blank to be returned - both will allow you to sum
the results.

Hope this helps.

Pete
Jan 9 @ 11:45am

Reply to this Message

rbell - 12 Jan 2006 02:06 GMT

Thanks a lot Pete. I got it working, I had left the (=) in when I put my
formula in so that caused me some problems, copy & paste.

Signature

rbell

Reply to this Message

Pete_UK - 12 Jan 2006 10:05 GMT

Thanks for the feedback - glad you got it working.

Pete

Reply to this Message