{"id":801,"date":"2022-07-26T06:03:00","date_gmt":"2022-07-26T11:03:00","guid":{"rendered":"http:\/\/xlfxs.com\/?p=801"},"modified":"2022-07-26T06:03:00","modified_gmt":"2022-07-26T11:03:00","slug":"searchingtablein2dimensions","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=801","title":{"rendered":"Searching table in 2 dimensions."},"content":{"rendered":"\n

Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis.<\/p>\n\n\n\n

This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.<\/p>\n\n\n\n\n\n\n\n

Back in 1997, one of the tricks I learned and was the reason for me to start loving Microsoft Excel, is this …<\/p>\n\n\n\n

Combining two (or more) functions to search a table in two dimensions.<\/p>\n\n\n\n

We can use…<\/p>\n\n\n\n

VLOOKUP + MATCH<\/h3>\n\n\n\n

Since VLOOKUP can search vertically (the V in VLOOKUP) we just need to use another function to search horizontally.<\/p>\n\n\n\n

MATCH fits perfect because it returns the number of cell returned, which exactly what VLOOKUP needs<\/p>\n\n\n\n

HLOOKUP + MATCH<\/h3>\n\n\n\n

In similar fashion, HLOOKUP will search horizontally (H in HLOOKUP), so we need a function to do the search in vertical. Yes, it is MATCH again, perfect match<\/p>\n\n\n\n

INDEX + 2 MATCHes<\/h3>\n\n\n\n

INDEX function does not do any search, it is basically calling the X and Y for a table, that is why we need 2 MATCHes, one vertical, and the other horizontal.<\/p>\n\n\n\n

OFFSET + 2 MATCHes<\/h3>\n\n\n\n

OFFSET, is one of the most powerful formulas in Excel, I used it a lot (along with INDIRECT, ADDRESS, etc) to do bunch of things, here we will see how it helps us extracting from table, with the help of 2 MATCHes<\/p>\n\n\n\n

We will use same table and same reference cells to show the similarities (and differences) among those 4 ways to do the search.<\/p>\n\n\n\n

So, if we have our table in Sheet1, in cells B2:H22, full reference will be Sheet1!$B$2:$H$22<\/p>\n\n\n\n

Then we put the ID we want to search for in vertical in Sheet2 cell B3<\/p>\n\n\n\n

The column name we want to search for in Sheet2 C2<\/p>\n\n\n\n

Then in C3 will be one of those formulas:<\/p>\n\n\n\n

=Vlookup($B3, Sheet1!$B$2:$H$22, Match(C$2, Sheet1!$B$2:$H$2, 0), False)<\/code><\/p>\n\n\n\n

=Hlookup(C$2, Sheet1!$B$2:$H$22, Match($B3, Sheet1!$B$2:$B$22, 0), False)<\/code><\/p>\n\n\n\n

=Index(Sheet1!$B$2:$H$22, Match($B3, Sheet1!$B$2:$B$22, 0) +1, Match(C$2, Sheet1!$B$2:$H$2, 0) +1)<\/code><\/p>\n\n\n\n

=Offset( Sheet1!$B$2, Match($B3, Sheet1!$B$2:$B$22, 0) -1, Match(C$2, Sheet1!$B$22:$H$2, 0) - 1, 1, 1)<\/code><\/p>\n\n\n\n

Now that we see the 4 combination, we can easily find differences (and similarities) among them.<\/p>\n\n\n\n

To describe those in English, the search funcions are VLOOKUO, HLOOKUP and MATCH.<\/p>\n\n\n\n

While INDEX and OFFSET do not do any search, that is why we needed 2 MATCHes with each, while we inly needed one MATCH with VLOOKUP or HLOOKUP.<\/p>\n\n\n\n

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

Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis. This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.<\/p>\n","protected":false},"author":1,"featured_media":1508,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,78,84,36,37,6,62],"tags":[144,149,158,183,206],"class_list":["post-801","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-formulas","category-hlookup","category-index","category-lookup_and_references","category-match","category-standard_functions","category-vlookup","tag-hlookup","tag-index","tag-match","tag-search","tag-vlookup"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/801","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=801"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/801\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/1508"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=801"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=801"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=801"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}