Office Q&A: A dynamic lookup solution for matching partial lists to a master list in Excel
Rob has several sheets that track attendance by the month. The pool of people is always the same, but each monthly list is different. He needs to track attendance, which is easy enough sheet by sheet. However, his requirement is a bit more complex because he needs to compare monthly attendance to a master list. In other words, for any given month, Rob needs to know who attended and who didn’t and the total attendance for that month. The monthly sheets rarely contain the entire list of possible attendees, but they could.
The easiest solution would be to generate a master list for each month and note who attends and who doesn’t in a second column. If this list is long, a simple filter would return those in, or not in, attendance, along with a quick count of both.
Unfortunately, Ron’s working with existing data, and while that solution might work going forward, it doesn’t help him with the existing data. What makes the most sense to me is a sheet that contains a master list of names and a dynamic solution that compares each monthly (partial) list to the master list. In this article, we’ll build this solution as follows:
- We’ll add a new sheet that contains a master list.
- We’ll add two literal strings to create a dynamic sheet reference to the partial lists.
- We’ll use the VLOOKUP() function to match the monthly partial lists to the master list.
- We’ll add a counting expression to the master list sheet to count the number in attendance each month.
I’m using Excel 2016 on a Windows 10 64-bit system but this will work through Excel 2003. For your convenience, you can download the demonstration .xlsx or .xls file.
Figure A shows three simple sheets (top) that are representative of Rob’s situation. As you can see, the lists are different on each sheet. (It’s possible that the sheets might be the same, but it doesn’t matter to the solution.)
We need to compare these simple lists (top) to a master list (bottom). Rob didn’t have a master list sheet.
The first step is to make sure the monthly lists are consistently positioned from sheet to sheet. That way, a single function can reference all partial lists. In other words, each list must start in the same cell in each sheet—for our example that’s B3. Rob’s lists are consistent already, so we don’t need to move anything, but whether you’re working with existing data or creating something new, consistency across sheets should be your first step.
Add the master list sheet
To create the master list sheet (also shown in Figure A) simply add a sheet and enter the complete list of potential attendees. Both names are necessary to compare; first and last names could (and often do) repeat. To this end, concatenate the first and last names on all sheets by adding the simple formula to column D, as shown in Figure B:
=B4 & ” ” & C4
Concatenate the first and last names.
There’s a space character between the quotation marks. Regardless of the values you’re combining, the resulting values must be unique.
Rob wants to compare the master list to each partial list for a count by month and he wants a visual clue indicating who did not attend. Figure C shows the next step toward satisfying these requirements:
- C1 is an input cell that Rob updates by entering the name of a partial list sheet (month).
- C2 is the largest possible range that the partial list of names on each sheet can comprise. The reference always begins with D4 and ends with D6 because there are only three records in the master list. You can update this reference if the list grows. It doesn’t need to match each partial list exactly either—empty cells aren’t a problem.
- C3 contains the formula =C1&”!”&C2 to concatenate the references in C1 and C2. The resulting string refers to a partial list on one of the partial list sheets, depending on the sheet name in C1.
Building the dynamic comparison solution.
Inserting rows to accommodate the input values on the master list sheet isn’t a problem, so don’t worry if the range of names in the full name list doesn’t match the partial lists. Consistency is important for the partial list sheets but not for the master list sheet.
Next, in the master list sheet, add the following VLOOKUP() function to column E:
This formula compares the master list to the partial list on the referenced sheet (C1) as shown in Figure D. In January (C1), Susan Harkins and Bill Harkins were present; Susan Smith wasn’t. Change the value in C1 to February and watch the values in E update accordingly (Figure E).
The VLOOKUP() function uses the string in C3 to compare the full list to a partial list on the referenced sheet.
Changing the month (sheet name) updates the results in column E.
The VLOOKUP() function uses the following syntax:
=VLOOKUP(lookupvalue, lookuptable, col, [range])
In our formula, lookupvalue is the concatenated name in column D of the master list sheet and lookuptable references a partial list on one of the monthly sheets by using the INDIRECT() function to create a valid reference from the concatenated sheet name and range strings in C1 and C2. The IFNA() function returns the value 1 instead of an error value when lookupvalue isn’t found. As a result, a match returns the value; no match returns 1.
Rob has one more request: He wants an attendance count per month. There are a number of ways to accomplish this, but the simplest is a counting expression function that references the values in column D, as shown in Figure F.
A counting expression returns the number in attendance each month.
combines two counting functions. COUNTA() returns the number of cells in the referenced range that aren’t empty. COUNT() returns the number of cells that contain numeric values. In February there are three non-empty cells and two cells that contain a numeric value, 3-2=1. Rob could just as easily use a single COUNT() function to return the number who are absent.
To use the master list sheet, Rob changes the sheet name in C1. He can add new monthly (partial list) sheets or delete old ones without affecting the master list sheet. However, he must maintain the range reference in C2 if the master list increases. In truth, he doesn’t even have to do that. He could enter a partial list range that includes blank cells to accommodate a growing master list. For instance, if C2 were $D$4:$D$20, Rob could add several new names to the master list before he had to update the string reference in C2 to accommodate more.
Users often think of sheets as a good way to divide related data by some category. In Rob’s case, the original structure impedes growth. When planning, you don’t always know where information needs will take you, but a sound foundation will improve your odds of expanding your work without too much angst. In Rob’s case, each monthly sheet could contain the same master list with a column that identifies attendance using Yes/No (or something similar). Or he could use one sheet and create a matrix of sorts by including each month in a column. Either structure would more easily accommodate growth than what he’s currently using.
Speaking of a matrix structure, Rob could show all months in the same sheet by adding monthly columns, but that takes a bit more work to maintain because he’d have to add a new column each month. However, if that’s what he needs, it could be easily accomplished.
There might be ways to improve my dynamic master sheet solution or you might have a completely different alternative. Please share your ideas in the Comments section below.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at [email protected]