#n/a

R

rbell

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
 
P

Pete_UK

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
 
R

rbell

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