If I've understood you correctly, I'd suggest that you use an
AutoFilter on each sheet to filter the data you need and copy it to a
second workbook. Repeat for each of your 55 sheets copying underneath
the last set of data.

To speed the task up you could use a macro to do the whole thing if
you're comfortable with VBA.
Otherwise a half way house would be to record a macro to do the
autofiltering on one sheet, and play this back on each of your 55
sheets, doing the copying and pasting manually.

HTH

Richard Buttrey
__

Hi

I would "bite the bullet" and do a once only copy and paste the data from
all 55 sheets onto one sheet, each set of data one block below each other to
create around 8000 rows.

I would do this in a brand new workbook, copying each sheet one at a time to
Sheet 1 of the new workbook. Once finished, you can forget about the old
workbook and do everything you want with the new one.

I would have an extra column, entitled whatever the name is for the source
sheet, and having copied the block of data from any given sheet, I would
then copy down that sheet name alongside its data.

Mark your header row and Data>Filter>Autofilter.
The dropdown on the "sheet name" would show just the data from that sheet.

You can then use Vlookup very easily on this one set of data.
Regards

Roger Govier