{"id":4920,"date":"2019-03-20T11:44:52","date_gmt":"2019-03-20T16:44:52","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4920"},"modified":"2020-02-05T13:27:08","modified_gmt":"2020-02-05T18:27:08","slug":"do-you-know-excel-vlookup","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4920","title":{"rendered":"Do you know \u2026 Excel VLOOKUP?"},"content":{"rendered":"

I frequently need to correlate two sets of data \u2013 generally information about accounts, where the logon ID will be found in both data sets. I\u2019ve imported my information into Access, defined a relationship between the two tables, and used a query to correlate my data. I\u2019ve written quick scripts to pull the data into an associative array for correlation. These are not quick<\/em> approaches.<\/p>\n

Using the VLOOKUP<\/a> function in Excel, you can search through data in rows and retrieve values from the record\u2019s other columns. HLOOKUP<\/a> provides the same function, but searches data in columns and retrieves values from the record\u2019s other rows (Vertical Lookup and Horizontal Lookup).<\/p>\n

Today, I have a list of individuals with their reporting structure and need to identify which accounts have Skype for Business provisioned.<\/p>\n

\"\"<\/a><\/p>\n

The Skype user list is, unfortunately, comes from a different program.<\/p>\n

\"\"<\/a><\/p>\n

To lookup user IDs from the first table against the Skype info in the second table, I use =VLOOKUP(B2,S4BInfo!A:B,2,FALSE)<\/p>\n

The first parameter in the function is the information you want to find, the second parameter is the area where you\u2019ll be looking for the data, the third parameter is the column in that range<\/em> that you want to return when a match is found. The fourth parameter indicates if you want to find the closest match (\u2018TRUE\u2019) or an exact match (\u2018FALSE\u2019). So my formula says \u201cfind the value in B2 within columns A and B of the SBInfo tab. Return the value from column 2 of that range, and I want an identical match\u201d.<\/p>\n

Note that the third parameter column number may not match the column number in the sheet \u2013 if I used the range C:D from the table below, I would still want to return the data in column 2 because my target data is still the second column in the search range.<\/p>\n

\"\"<\/a><\/p>\n

Fill down and I have a single table that contains both the reporting information that I needed and a column indicating if the individual has a Skype for Business account<\/p>\n

\"\"<\/a><\/p>\n

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

I frequently need to correlate two sets of data \u2013 generally information about accounts, where the logon ID will be found in both data sets. I\u2019ve imported my information into Access, defined a relationship between the two tables, and used a query to correlate my data. I\u2019ve written quick scripts to pull the data into …<\/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-4920","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\/4920","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=4920"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4920\/revisions"}],"predecessor-version":[{"id":5947,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4920\/revisions\/5947"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4920"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}