{"id":799,"date":"2017-06-09T01:19:07","date_gmt":"2017-06-08T19:19:07","guid":{"rendered":"http:\/\/xlfxs.com\/?p=799"},"modified":"2017-06-09T01:19:07","modified_gmt":"2017-06-08T19:19:07","slug":"offsetmatch","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=799","title":{"rendered":"OFFSET+MATCH"},"content":{"rendered":"

One of the power of combining functions in one cell is using the best of these functions<\/p>\n

An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.<\/p>\n

Why do we want to do that?<\/h3>\n

VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time<\/p>\n

We needed several times to locate a row by using a column on the right, then get the value from a column left to it.<\/p>\n

This is when VLOOKUP can not help us.<\/p>\n

<\/p>\n

And this is when a combined function like OFFSET + MATCH can get the job done.<\/p>\n

So, if we have a table as below<\/p>\n

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

A simple VLOOKUP can help us find any of these values, say “Test_Country”<\/p>\n

=VLOOKUP(\"Test_Country\",AB:AC,2,FALSE)<\/pre>\n
Which will return the expected result.<\/p>\n
However, a table as below, we cannot use VLOOKUP if we want to search using “TreeKey” column, or “UnitID”, basically any column on the right of the table<\/p>\n
\"\"<\/a><\/p>\n
Since VLOOKUP searches only left-most column of table.<\/p>\n
\"\"<\/a><\/p>\n
And if we try using -ve number in “Col_index_num” we get an error<\/p>\n
 \"\"<\/a><\/p>\n
So, what can we do?<\/h3>\n
Use combination of OFFSET and MATCH<\/p>\n
MATCH will do the actual search in the needed column, while OFFSET gives me the actual return as below<\/p>\n
below example will search for “T1_3_1_9” in column “TreeKey” column and returns the “PVID” of that item<\/p>\n
=OFFSET($DM$1,MATCH(\"T1_3_1_9\",DR:DR,0),2)<\/pre>\n
Or, we can search for “FC12_1B500733244144” in “UnitID” column and return its “PVID” as below<\/p>\n
=OFFSET($DM$1,MATCH(\"FC12_1B500733244144\",DP:DP,0),2)<\/pre>\n
Hope this will help<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the power of combining functions in one cell is using the best of these functions An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP. Why do we want to do that? VLOOKUP will search in the left-most column in a table to get a value from…<\/p>\n
Read More “OFFSET+MATCH”<\/span> »<\/a><\/p>\n","protected":false},"author":1,"featured_media":1088,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,36,37,47,6],"tags":[158,173,206],"class_list":["post-799","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-formulas","category-lookup_and_references","category-match","category-offset","category-standard_functions","tag-match","tag-offset","tag-vlookup"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/799","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=799"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/799\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/1088"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}