Instead of using say, in B1: = VLOOKUP(A1,Sheet2!A:B,2,0)
Use an " =IF(ISNA(VLOOKUP(...)),0, VLOOKUP(...))"
error-trap construct to return zeroes for any non-matches instead of #NAs.
For example, you could use in B1:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1, Sheet2!A:B,2,0))
Downstream SUMs, etc will now work ok.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sailor" wrote in message
...
Excell 2002
I am using numerous VLOOKUP functions (including the "FALSE" option)
accross
a row to extract exact values from the same range, which may or may not
contain the look-up value. I then want to sum accross the row to give me
a
running total. The problem is that VLOOKUP returns "#N/A" when an exact
match is not found; which then returns a "#N/A" error in the sum function.
|