This sounds like a job for Access, not Excel...
Are there definable features that will enable you to determine when you have run out of data? (Like, always starts at B2, runs down B until the cel value is "", or something like that.)
Could you explain a little more what the "look [up] a value in a pivot table" means, in terms of manifestation? Move to that cell? Send the value to a file? Put all the values thus found into an array? ...?