Perhaps if you post a sample of the file you're referring to this may help to understand your request (not even sure you can do this here). However, it seems to me the best solution for you might be pivottables. Use a dynamically adjusting range name to name the source data and then do the reports and analysis you need via a pivottable or multiple pivottables.
---
frmsrcurl:
http://msgroups.net/microsoft.public...cro-or-Look-up