In article ,
"Laura" wrote:

Domenic:

Sorry to bother you again, but I have one more question.

No problem...

How do I keep the formula from returning #N/A when the value is not
found?

One option...

Enter the following formula in a cell, let's say C1:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:10"))&"!A2:A100"),B1
))

....where B1 contains your lookup value.

Enter the following formula in another cell, let's say D1:

=IF(N(C1),VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,C OUNTIF(INDIRECT("Sheet"
&ROW(INDIRECT("1:10"))&"!A2:A100"),B1)0,0)&"!A2:F 100"),6,0),"")

....confirmed with CONTROL+SHIFT+ENTER.

Another option...

Use conditional formatting to hide the errors. Assuming that the
formula is entered in D1:

1) Select D1

2) Format Conditional Formatting Formula Is

3) Enter the following formula:

=ISNA(D1)

4) Choose 'White' as your font colour

5) Click Ok

Hope this helps!