I mentioned yesterday that we’re creating groups based on the upper level manager through whom individuals report. Since my groups are based on the upper level managers, I need to be able to identify when a new individual pops into the list of upper level managers. Real upper level management doesn’t change frequently, but unfilled positions create gaps in the reporting structure. I call the manager before the gap the highest-ranking person in that vertical and that individual’s reporting subtree becomes a group.

Determining if values from one list appear in another list is easy in Microsoft Access – it’s an unmatched query. I’d rather not have to switch between the two programs, and I was certain an Excel formula could do the same thing. It can!

The formula is:

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

And it does flag any manager from column H that does not appear in my list of upper level managers.

I am also able to filter my spreadsheet to display only records where the upper level manager does not appear in my summary table.

What is my formula doing? It is a combination of three functions

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

It starts with the IF function – a logical comparison – which is used as if(Test,ResultIfTestIsTrue, ResultIfTestIsFalse).

If the test is true, “Not in Manager Summary” will be put into the cell. If the test is false, nothing (“”) will be put into the cell.

The test itself is two functions. I’ve documented the VLOOKUP function previously, but briefly it searches a range of data for a specific value. If the value is found, it returns something. If the value isn’t found, it returns N/A.

In conjunction with the VLOOKUP, I am using the ISNA function. This function is a logic test – it returns TRUE when the value is N/A and FALSE otherwise.

So my formula says “Look for the value of cell H2 in column A of the SOA6MgrSummary tab. If the result is N/A, put ‘Not in Manager Summary’ in this cell, otherwise leave this cell empty”.