{"id":1004,"date":"2019-09-15T11:50:27","date_gmt":"2019-09-15T03:50:27","guid":{"rendered":"http:\/\/www.fyndpro.com\/?p=1004"},"modified":"2026-08-27T16:15:30","modified_gmt":"2026-08-27T08:15:30","slug":"index_match","status":"publish","type":"post","link":"https:\/\/www.fyndpro.com\/en\/index_match\/","title":{"rendered":"Fix VLOOKUP's inability to look left"},"content":{"rendered":"<blockquote><p>In this situation, manually copying Column A to Column C (moving the lookup to the right) and then running VLOOKUP is too inefficient. If there are many rows to copy, it could even crash the computer...<\/p><\/blockquote>\n\t\t\t\t\t\t<style>\r\n\t\t\t\t<style>\r\n#wpsm_accordion_1002 .wpsm_panel-heading{\r\n\tpadding:0px !important;\r\n}\r\n#wpsm_accordion_1002 .wpsm_panel-title {\r\n\tmargin:0px !important; \r\n\ttext-transform:none !important;\r\n\tline-height: 1 !important;\r\n}\r\n#wpsm_accordion_1002 .wpsm_panel-title a{\r\n\ttext-decoration:none;\r\n\toverflow:hidden;\r\n\tdisplay:block;\r\n\tpadding:0px;\r\n\tfont-size: 18px !important;\r\n\tfont-family: Open Sans !important;\r\n\tcolor:#000000 !important;\r\n\tborder-bottom:0px !important;\r\n}\r\n\r\n#wpsm_accordion_1002 .wpsm_panel-title a:focus {\r\noutline: 0px !important;\r\n}\r\n\r\n#wpsm_accordion_1002 .wpsm_panel-title a:hover, #wpsm_accordion_1002 .wpsm_panel-title a:focus {\r\n\tcolor:#000000 !important;\r\n}\r\n#wpsm_accordion_1002 .acc-a{\r\n\tcolor: #000000 !important;\r\n\tbackground-color:#e8e8e8 !important;\r\n\tborder-color: #ddd;\r\n}\r\n#wpsm_accordion_1002 .wpsm_panel-default > .wpsm_panel-heading{\r\n\tcolor: #000000 !important;\r\n\tbackground-color: #e8e8e8 !important;\r\n\tborder-color: #e8e8e8 !important;\r\n\tborder-top-left-radius: 0px;\r\n\tborder-top-right-radius: 0px;\r\n}\r\n#wpsm_accordion_1002 .wpsm_panel-default {\r\n\t\tborder:1px solid transparent !important;\r\n\t}\r\n#wpsm_accordion_1002 {\r\n\tmargin-bottom: 20px;\r\n\toverflow: hidden;\r\n\tfloat: none;\r\n\twidth: 100%;\r\n\tdisplay: block;\r\n}\r\n#wpsm_accordion_1002 .ac_title_class{\r\n\tdisplay: block;\r\n\tpadding-top: 12px;\r\n\tpadding-bottom: 12px;\r\n\tpadding-left: 15px;\r\n\tpadding-right: 15px;\r\n}\r\n#wpsm_accordion_1002  .wpsm_panel {\r\n\toverflow:hidden;\r\n\t-webkit-box-shadow: 0 0px 0px rgba(0, 0, 0, .05);\r\n\tbox-shadow: 0 0px 0px rgba(0, 0, 0, .05);\r\n\t\tborder-radius: 4px;\r\n\t}\r\n#wpsm_accordion_1002  .wpsm_panel + .wpsm_panel {\r\n\t\tmargin-top: 5px;\r\n\t}\r\n#wpsm_accordion_1002  .wpsm_panel-body{\r\n\tbackground-color:#ffffff !important;\r\n\tcolor:#000000 !important;\r\n\tborder-top-color: #e8e8e8 !important;\r\n\tfont-size:16px !important;\r\n\tfont-family: Open Sans !important;\r\n\toverflow: hidden;\r\n\t\tborder: 2px solid #e8e8e8 !important;\r\n\t}\r\n\r\n#wpsm_accordion_1002 .ac_open_cl_icon{\r\n\tbackground-color:#e8e8e8 !important;\r\n\tcolor: #000000 !important;\r\n\tfloat:right !important;\r\n\tpadding-top: 12px !important;\r\n\tpadding-bottom: 12px !important;\r\n\tline-height: 1.0 !important;\r\n\tpadding-left: 15px !important;\r\n\tpadding-right: 15px !important;\r\n\tdisplay: inline-block !important;\r\n}\r\n\r\n\t\t\t\r\n\t\t\t<\/style>\t\r\n\t\t\t<\/style>\r\n\t\t\t<div class=\"wpsm_panel-group\" id=\"wpsm_accordion_1002\" >\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1002 \" href=\"javascript:void(0)\" data-target=\"#ac_1002_collapse1\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-plus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tThe issue\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1002_collapse1\" class=\"wpsm_panel-collapse collapse\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\tIn this case, copying Column A to Column C (moving the lookup to the right) and then running VLOOKUP is inefficient.\r\n\r\nSo this time we'll introduce INDEX + MATCH to replace VLOOKUP.\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"collapsed\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1002 \" href=\"javascript:void(0)\" data-target=\"#ac_1002_collapse2\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-plus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tHow INDEX + MATCH works\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1002_collapse2\" class=\"wpsm_panel-collapse collapse\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\t<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1003\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/09\/%E8%A7%A3%E6%B1%BAVLOOKUP%E4%B8%8D%E8%83%BD%E5%90%91%E5%B7%A6%E6%9F%A5%E6%89%BE%E7%9A%84%E5%95%8F%E9%A1%8C2.png?resize=371%2C278\" alt=\"\" width=\"371\" height=\"278\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/09\/%E8%A7%A3%E6%B1%BAVLOOKUP%E4%B8%8D%E8%83%BD%E5%90%91%E5%B7%A6%E6%9F%A5%E6%89%BE%E7%9A%84%E5%95%8F%E9%A1%8C2.png?w=371&amp;ssl=1 371w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/09\/%E8%A7%A3%E6%B1%BAVLOOKUP%E4%B8%8D%E8%83%BD%E5%90%91%E5%B7%A6%E6%9F%A5%E6%89%BE%E7%9A%84%E5%95%8F%E9%A1%8C2.png?resize=150%2C112&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/09\/%E8%A7%A3%E6%B1%BAVLOOKUP%E4%B8%8D%E8%83%BD%E5%90%91%E5%B7%A6%E6%9F%A5%E6%89%BE%E7%9A%84%E5%95%8F%E9%A1%8C2.png?resize=300%2C225&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/09\/%E8%A7%A3%E6%B1%BAVLOOKUP%E4%B8%8D%E8%83%BD%E5%90%91%E5%B7%A6%E6%9F%A5%E6%89%BE%E7%9A%84%E5%95%8F%E9%A1%8C2.png?resize=230%2C172&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/09\/%E8%A7%A3%E6%B1%BAVLOOKUP%E4%B8%8D%E8%83%BD%E5%90%91%E5%B7%A6%E6%9F%A5%E6%89%BE%E7%9A%84%E5%95%8F%E9%A1%8C2.png?resize=350%2C262&amp;ssl=1 350w\" sizes=\"auto, (max-width: 371px) 100vw, 371px\" \/>\r\n\r\n<hr \/>\r\n\r\nIf we want to use \"30\" to find \"C\", we actually need the 4th cell in Column A, and since 30 happens to be in the 4th cell of Column B, if we can find this \"4\" we can likely complete the task. To find this 4, use MATCH.\r\n<pre>=MATCH(30,B:B,1)<\/pre>\r\nThat means telling Excel to find which position in Column B contains 30 \u2014 it's 4.\r\n\r\nThen we use that 4 as the row number for Column A to retrieve its 4th cell, which is done with INDEX.\r\n<pre>=INDEX(A:A,MATCH(30,B:B,1))<\/pre>\r\nSo you can see INDEX + MATCH achieves VLOOKUP's effect without being restricted by lookup direction!\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"collapsed\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1002 \" href=\"javascript:void(0)\" data-target=\"#ac_1002_collapse3\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-plus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tExcel Functions Workshop\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1002_collapse3\" class=\"wpsm_panel-collapse collapse\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\t<blockquote>Also, a reminder that this weekend's offer \u3010Early bird discount: $200 off\u3011 ends soon!<\/blockquote>\r\n<strong>Excel Functions Workshop:<\/strong>\r\n<ul>\r\n \t<li>Introduce and explain the usage of nearly 80 functions<\/li>\r\n \t<li>Explore their usage and characteristics through common workplace problems<\/li>\r\n \t<li>Help you establish relationships between datasets<\/li>\r\n \t<li>Teach you how to solve many of the problems you encounter at work<\/li>\r\n<\/ul>\r\n<a href=\"https:\/\/www.fyndpro.com\/index.php\/workshops\/\">\u3010Details\u3011<\/a>\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t<\/div>\r\n\t\t\t\r\n<script type=\"text\/javascript\">\r\n\t\r\n\t\tfunction do_resize(){\r\n\r\n\t\t\tvar width=jQuery( '.wpsm_panel .wpsm_panel-body iframe' ).width();\r\n\t\t\tvar height=jQuery( '.wpsm_panel .wpsm_panel-body iframe' ).height();\r\n\r\n\t\t\tvar toggleSize = true;\r\n\t\t\tjQuery('iframe').animate({\r\n\t\t\t    width: toggleSize ? width : 640,\r\n\t\t\t    height: toggleSize ? height : 360\r\n\t\t\t  }, 250);\r\n\r\n\t\t\t  toggleSize = !toggleSize;\r\n\t\t}\r\n\t\t\r\n<\/script>","protected":false},"excerpt":{"rendered":"<p>\u9047\u5230\u9019\u500b\u60c5\u6cc1\uff0c\u5982\u679c\u91cd\u65b0\u628aColumn A\u6284\u81f3Column C (\u628a\u76ee\u6a19\u642c\u5230\u53f3\u908a)\uff0c\u7136\u5f8c\u505a\u4e00\u6b21 VLOOKUP\uff0c [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3793,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[8,20],"tags":[296,3,4,90,281],"class_list":["post-1004","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-onlinesupport","tag-excel-functions","tag-index","tag-match","tag-vlookup","tag-data-lookup"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2026\/08\/new_covers_batch_03_1785_excel.jpg?fit=816%2C1088&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1004","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/comments?post=1004"}],"version-history":[{"count":1,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1004\/revisions"}],"predecessor-version":[{"id":1006,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1004\/revisions\/1006"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/3793"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=1004"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=1004"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=1004"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}