{"id":5078,"date":"2019-04-04T21:04:15","date_gmt":"2019-04-05T02:04:15","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=5078"},"modified":"2019-04-04T21:04:38","modified_gmt":"2019-04-05T02:04:38","slug":"did-you-know-you-can-perform-an-unmatched-query-in-excel","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=5078","title":{"rendered":"Did you know \u2026 you can perform an \u201cunmatched query\u201d in Excel?"},"content":{"rendered":"

I mentioned yesterday that we\u2019re 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<\/em> individual pops into the list of upper level managers. Real<\/em> upper level management doesn\u2019t 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\u2019s reporting subtree becomes a group.<\/p>\n

Determining if values from one list appear in another list is easy in Microsoft Access \u2013 it\u2019s an unmatched query. I\u2019d rather not have to switch between the two programs, and I was certain an Excel formula could do the same thing. It can!<\/p>\n

The formula is:<\/p>\n

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)<\/p>\n

And it does<\/em> flag any manager from column H that does not appear in my list of upper level managers.<\/p>\n

<\/p>\n

I am also able to filter my spreadsheet to display only<\/em> records where the upper level manager does not appear in my summary table.<\/p>\n

<\/p>\n

What is my formula doing? It is a combination of three functions<\/p>\n

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)<\/p>\n

It starts with the IF function<\/a> \u2013 a logical comparison \u2013 which is used as if(Test,ResultIfTestIsTrue, ResultIfTestIsFalse).<\/p>\n

If the test is true, \u201cNot in Manager Summary\u201d will be put into the cell. If the test is false, nothing (\u201c\u201d) will be put into the cell.<\/p>\n

The test<\/em> itself is two functions. I\u2019ve documented the VLOOKUP function<\/a> 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\u2019t<\/em> found, it returns N\/A.<\/p>\n

In conjunction with the VLOOKUP, I am using the ISNA function. This function is a logic test \u2013 it returns TRUE when the value is<\/em> N\/A and FALSE otherwise.<\/p>\n

So my formula says \u201cLook for the value of cell H2 in column A of the SOA6MgrSummary tab. If the result is N\/A, put \u2018Not in Manager Summary\u2019 in this cell, otherwise leave this cell empty\u201d.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

I mentioned yesterday that we\u2019re 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\u2019t change frequently, but unfilled …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-5078","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5078","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5078"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5078\/revisions"}],"predecessor-version":[{"id":5081,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5078\/revisions\/5081"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5078"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5078"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5078"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}