Sorry for the vague title, but I wasn't sure how best to explain it. And I'm not good at explaining things, so apologies if
things get confused.
I have 2 spreadsheets: the first has over 300 worksheets containing relatively the same
information, and the second is a single sheet containing a list of names separated out into First name, Surname, date of
birth. I need to look up each of the names on the smaller spreadsheet and indicate with a 'yes' if the name appears within
the larger spreadsheet in the 'Access' section (explained below). That is the basic Vlookup across multiple sheets from the
The 'additional criteria' bit is the problem.
The large spreadsheet is a csv file pulled off a
database. The information within is split up into separate sheets for each of our offices, and on each sheet the information
is split again between those members of staff with access and those without access. So a sheet might look like this:
First Name Surname Date of Birth
John Smith 04/09/1984
Steve Jones 15/04/1967
Dave Hammond 16/12/1954
Jenny Accord 08/11/1982
The problem is that I only need to know those people from the first spreadsheet that have access (I don't need to know what
office they're in or on which sheet they appear, merely whether they have access or not), and each section (Access, No
Access) is separated only by a header, which, though always in column A as above, won't always be in the same place as each
office has a different number of people within it (meaning that the sections will be different sizes depending on the number
of staff). Also, there is the added problem that each sheet isn't necessarily split the same way, i.e. with Access at the top
and No Access underneath (as above). Some of the sheets are split the otherway around (No Access first, Access
Is there anyway a vlookup could be concocted on my smaller spreadsheet that will first look up the
name in every sheet in the larger spreadsheet and, once it's found it, look up column A and discard the name if it finds No
Access first and keep it if it finds Access first.
I hope that makes sense.