{"id":1067,"date":"2019-12-29T10:38:43","date_gmt":"2019-12-29T02:38:43","guid":{"rendered":"http:\/\/www.fyndpro.com\/?p=1067"},"modified":"2026-08-27T16:15:25","modified_gmt":"2026-08-27T08:15:25","slug":"multi_lookup_value","status":"publish","type":"post","link":"https:\/\/www.fyndpro.com\/en\/multi_lookup_value\/","title":{"rendered":"Multi-criteria lookup"},"content":{"rendered":"<blockquote><p>When there is only one lookup value, many people know to use VLOOKUP. But if there are two or three lookup values, can VLOOKUP still work? How do you set the lookup values?<\/p><\/blockquote>\n\t\t\t\t\t\t\t<h3 style=\"margin-bottom:20px;display:block;width:100%;margin-top:10px\">Multi-criteria lookup <\/h3>\r\n\t\t\t\t\t\t<style>\r\n\t\t\t\t<style>\r\n#wpsm_accordion_1066 .wpsm_panel-heading{\r\n\tpadding:0px !important;\r\n}\r\n#wpsm_accordion_1066 .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_1066 .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_1066 .wpsm_panel-title a:focus {\r\noutline: 0px !important;\r\n}\r\n\r\n#wpsm_accordion_1066 .wpsm_panel-title a:hover, #wpsm_accordion_1066 .wpsm_panel-title a:focus {\r\n\tcolor:#000000 !important;\r\n}\r\n#wpsm_accordion_1066 .acc-a{\r\n\tcolor: #000000 !important;\r\n\tbackground-color:#e8e8e8 !important;\r\n\tborder-color: #ddd;\r\n}\r\n#wpsm_accordion_1066 .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_1066 .wpsm_panel-default {\r\n\t\tborder:1px solid transparent !important;\r\n\t}\r\n#wpsm_accordion_1066 {\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_1066 .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_1066  .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_1066  .wpsm_panel + .wpsm_panel {\r\n\t\tmargin-top: 5px;\r\n\t}\r\n#wpsm_accordion_1066  .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_1066 .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_1066\" >\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_1066 \" href=\"javascript:void(0)\" data-target=\"#ac_1066_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-minus\"><\/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\tUsing the LOOKUP formula\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_1066_collapse1\" class=\"wpsm_panel-collapse collapse in\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\tThis time we won't use VLOOKUP; we'll use its sibling, LOOKUP!\r\n\r\n<pre>=LOOKUP(1,0\/((E2=A2:A7)*(F2=B2:B7)),C2:C7)<\/pre>\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_1066 \" href=\"javascript:void(0)\" data-target=\"#ac_1066_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\tFormula explanation\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_1066_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=LOOKUP(1,0\/((E2=A2:A7)*(F2=B2:B7)),C2:C7)\r\n<ol>\r\n \t<li>Start from the middle: verify whether E2 equals each value in A2:A7. In other words, check whether A2:A7 equals \"\u4e2d\u74b0\" \u2014 TRUE if yes, otherwise FALSE.<\/li>\r\n \t<li>Similarly, check whether B2:B7 equals \"\u6771\u6d8c\" \u2014 TRUE if yes, otherwise FALSE.<\/li>\r\n \t<li>At this point Excel will have two arrays of TRUE and FALSE, and they are multiplied together, like this:\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1073\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?resize=525%2C244\" alt=\"\" width=\"525\" height=\"244\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?w=525&amp;ssl=1 525w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?resize=300%2C139&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?resize=150%2C70&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?resize=230%2C107&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?resize=350%2C163&amp;ssl=1 350w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-3.png?resize=480%2C223&amp;ssl=1 480w\" sizes=\"auto, (max-width: 525px) 100vw, 525px\" \/><\/li>\r\n \t<li>Since TRUE=1 and FALSE=0, only one pair TRUE*TRUE produces 1. The rest are divided by 0, as follows:\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1074\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?resize=527%2C245\" alt=\"\" width=\"527\" height=\"245\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?w=527&amp;ssl=1 527w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?resize=300%2C139&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?resize=150%2C70&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?resize=230%2C107&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?resize=350%2C163&amp;ssl=1 350w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2019\/12\/%E5%A4%9A%E6%A2%9D%E4%BB%B6%E6%9F%A5%E6%89%BE-4.png?resize=480%2C223&amp;ssl=1 480w\" sizes=\"auto, (max-width: 527px) 100vw, 527px\" \/><\/li>\r\n \t<li>Because any number divided by 0 results in an error and cannot be compared to 1, only the entry that is divided by 1 survives. Comparing that to 1 then naturally returns $80.<\/li>\r\n<\/ol>\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>\u7576Lookup Value\u53ea\u6709\u4e00\u500b\uff0c\u4e0d\u5c11\u4eba\u90fd\u61c2\u5f97\u7528VLOOKUP\u53bb\u67e5\u627e\u3002\u554f\u984c\u662f\u5982\u679cLookup Value\u6709\u5169 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3788,"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":[295,92,90,306,292],"class_list":["post-1067","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-onlinesupport","tag-excel-formulas","tag-lookup","tag-vlookup","tag-lookup-functions","tag-error-values"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2026\/08\/new_covers_batch_02_455_excel.jpg?fit=816%2C1088&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1067","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=1067"}],"version-history":[{"count":1,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1067\/revisions"}],"predecessor-version":[{"id":1070,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1067\/revisions\/1070"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/3788"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=1067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=1067"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=1067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}