VLOOKUP returns error if value is not in all sheets?

Hi, I'm pretty new to excel and very new to this forum so I'm sorry if this is an easy-to-answer question. My excel vocabulary is probably not the most accurate either, but if you can understand this any help you can provide would be much appreciated thank you!

I'm using VLOOKUP to return and average values from 10 sheets. When I drag the formula it works great, but only for the lookup values that are found in every sheet. Some lookup values are only in 2 or 3 sheets, but instead of giving me the average of those values it returns an error (#N/A).

Is there a way to tell the formula to lookup the values only if the lookup value is found in the sheet? I think that's the easiest way to put it. Thanks again for reading and hopefully I don't have to go in and edit each formula manually haha.

Cheers,
Mike

Can you provide us with a sample of your workbook and the formula?

Thanks

Hello,

1. Its better of you name the ranges and then use them in your formula. Read this article on how to do that.
http://office.microsoft.com/en-us/ex...010147120.aspx
It will make your formula look cleaner and less cluttered. I would name the range in each year something like " year2008" and so on, so your formula would look something like this:

Formula:

Please Login or Register  to view this content.

2. You need to place an IFERROR after each VLOOKUP ( if you have excel 2003 or earlier you will have to use the IF and ISNA to trap the error.
Here is a short version of how it should look

Formula:

Please Login or Register  to view this content.

Thanks a lot

Thank you! The named range makes it much easier. Very Much appreciated

I am glad I was able to help. If you question was solved and my help was beneficial; please do not forget to mark the thread as solved and add reputation.

Thanks