{"id":1641,"date":"2020-08-06T17:05:14","date_gmt":"2020-08-06T15:05:14","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1641"},"modified":"2024-02-06T16:04:45","modified_gmt":"2024-02-06T15:04:45","slug":"excel-named-ranges-with-vlookup-formula-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-named-ranges-with-vlookup-formula-excel\/","title":{"rendered":"Named Ranges with Vlookup Formula"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.<\/p>\n<p><em><strong>Formula breakdown:<\/strong><\/em><\/p>\n<p>=VLOOKUP(<span style=\"color: #0000ff;\">lookup_value<\/span>, <span style=\"color: #ff6600;\">table_array<\/span>, <span style=\"color: #008000;\">col_index_num<\/span>, <span style=\"color: #ff00ff;\">[range_lookup]<\/span>)<\/p>\n<p><em><strong>What it means:<\/strong><\/em><\/p>\n<p>=VLOOKUP(<span style=\"color: #0000ff;\">this value<\/span>, <span style=\"color: #ff6600;\">in this Named Range<\/span>, <span style=\"color: #008000;\">and get me value in this column<\/span>, <span style=\"color: #ff00ff;\">Exact Match\/FALSE\/0]<\/span>)<\/p>\n<hr \/>\n<p>&nbsp;<\/p>\n<p>A <em>Named Range<\/em> makes it easier to understand Excel <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a>, especially if the said formula contains an array argument.<\/p>\n<p>A <em>Named Range<\/em> can be a cell, a cell range, a Table, a function, or a constant.<\/p>\n\n<p>Watch this video on <a href=\"https:\/\/youtu.be\/HyH8sBik7nM\" target=\"_blank\" rel=\"nofollow noopener\"><strong>YouTube<\/strong> <\/a>to learn more about Vlookup Range:<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/HyH8sBik7nM?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\">Follow the step-by-step tutorial on how to use Vlookup with named range and make sure to download the workbook and follow along:<\/p>\n<p class=\"line-two\"><strong><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/Vlookup_Named-Rangesv2.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/Vlookup_Named-Rangesv2.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook <i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Vlookup_Named-Rangesv2.xlsx<\/span><\/a><\/strong><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<div class=\"jumbotron2\">\n<div class=\"meo-subcount-2\">\n<p><a id=\"om-f2q8uijmbbmwc3oohwuo-holder\" class=\"drop-shadow lifted btn btn-secondary\" href=\"https:\/\/app.monstercampaigns.com\/c\/f2q8uijmbbmwc3oohwuo\/\" target=\"_blank\" rel=\"nofollow noopener\">DOWNLOAD OUR<br \/>\nFREE EXCEL GUIDES<\/a><\/p>\n<p><!--<script type=\"text\/javascript\" src=\"https:\/\/lead.myexcelonline.com\/app\/js\/api.min.js\" data-campaign=\"f2q8uijmbbmwc3oohwuo\" data-user=\"52704\" async><\/script>--><\/p>\n<\/div>\n<\/div>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span>\u00a0To<strong> define a <em>Named Range<\/em><\/strong> in Excel you need to select the cell\/cell range\/Table\/function\/constant and go to the <em>Name Box<\/em> which is located on the top left-hand corner of the workbook &#8211; next to the <em>Formula Bar<\/em>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-01-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-5107\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-01-1.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"1016\" height=\"321\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-01-1.png 1016w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-01-1-300x95.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-01-1-768x243.png 768w\" sizes=\"(max-width: 1016px) 100vw, 1016px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2<\/strong>:<\/span>\u00a0In here you can name your range whatever you like (make sure there are no spaces) and press <em>Enter.<\/em>\u00a0 You can view your<em> Named Range<\/em> by clicking on the drop-down box in the <em>Name Box<\/em>. \u00a0In our example, we will give this a name of <strong>StockList.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-4529\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-02.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"565\" height=\"112\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-02.png 565w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-02-300x59.png 300w\" sizes=\"(max-width: 565px) 100vw, 565px\" \/><\/p>\n<p>You can also <strong>view\/edit\/delete your<\/strong> <em>Named Range<\/em> by going to the <em>Formulas<\/em> tab in the Ribbon menu and selecting <em>Name Manager<\/em>.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3<\/strong>:<\/span>\u00a0Now that you are all set, each time you are creating a formula, like a Vlookup formula, it is best to use a<em> Named Range<\/em> as it makes the formula easier to understand and maintain.<\/p>\n<p>We need to <strong>enter the <i>Vlookup\u00a0<\/i>function<\/strong>:<\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=VLOOKUP(<\/span><\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-4530\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-03.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"426\" height=\"157\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-03.png 426w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-03-300x111.png 300w\" sizes=\"(max-width: 426px) 100vw, 426px\" \/><\/p>\n<p>The Vlookup\u00a0arguments:<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-named-ranges-with-vlookup-formula-excel\/#lookup_value\" title=\"lookup_value\">lookup_value<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-named-ranges-with-vlookup-formula-excel\/#table_array\" title=\"table_array\">table_array<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-named-ranges-with-vlookup-formula-excel\/#col_index_num\" title=\"col_index_num\">col_index_num<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-named-ranges-with-vlookup-formula-excel\/#range_lookup\" title=\"[range_lookup]\">[range_lookup]<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"lookup_value\"><\/span><em><strong><span style=\"color: #0000ff;\">lookup_value<\/span><\/strong><\/em><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>What are we looking for?<\/strong><\/p>\n<p><em>Reference the cell that contains the text or value:<\/em><\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #000000;\">=VLOOKUP<\/span><span style=\"color: #0000ff;\">(G15,<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-04-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-5108\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-04-1.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"1017\" height=\"216\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-04-1.png 1017w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-04-1-300x64.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-04-1-768x163.png 768w\" sizes=\"(max-width: 1017px) 100vw, 1017px\" \/><\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"table_array\"><\/span><span style=\"color: #0000ff;\"><i>table_array<\/i><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>From which list are we doing a lookup on?<\/strong><\/p>\n<p><em>The formula for Excel VlookUp Named Range will be:<\/em><\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #000000;\">=VLOOKUP<\/span><span style=\"color: #0000ff;\"><span style=\"color: #000000;\">(G15,<\/span>\u00a0StockList,<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-05-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-5109\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-05-1.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"1022\" height=\"231\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-05-1.png 1022w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-05-1-300x68.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-05-1-768x174.png 768w\" sizes=\"(max-width: 1022px) 100vw, 1022px\" \/><\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"col_index_num\"><\/span><em><strong><span style=\"color: #0000ff;\">col_index_num<\/span><\/strong><\/em><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>From which column do we want to retrieve the value?<\/strong><\/p>\n<p><em>We want to\u00a0retrieve the Price which is the SECOND\u00a0column from our table array:<\/em><\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #000000;\">=VLOOKUP<\/span><span style=\"color: #0000ff;\"><span style=\"color: #000000;\">(G15,<\/span> <\/span><span style=\"color: #000000;\">StockList<\/span><span style=\"color: #0000ff;\"><span style=\"color: #000000;\">,<\/span>\u00a02,<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-06-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-5110\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-06-1.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"1021\" height=\"220\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-06-1.png 1021w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-06-1-300x65.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-06-1-768x165.png 768w\" sizes=\"(max-width: 1021px) 100vw, 1021px\" \/><\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"range_lookup\"><\/span><em><strong><span style=\"color: #0000ff;\">[range_lookup]<\/span><\/strong><\/em><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>Do we want an exact match?<\/strong><\/p>\n<p><em>Place in FALSE to signify that we want an exact match:<\/em><\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #000000;\">=VLOOKUP<\/span><span style=\"color: #0000ff;\"><span style=\"color: #000000;\">(G15,<\/span>\u00a0<\/span><span style=\"color: #000000;\">StockList<\/span><span style=\"color: #0000ff;\"><span style=\"color: #000000;\">,<\/span> <span style=\"color: #000000;\">2,<\/span>\u00a0FALSE)<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-4534\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-07.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"458\" height=\"160\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-07.png 458w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-07-300x105.png 300w\" sizes=\"(max-width: 458px) 100vw, 458px\" \/><\/a><\/p>\n<p>This is how the price will now dynamically change based on your selection with Vlookup using named range:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-08.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Named Ranges with Vlookup Formula\"  class=\"alignnone size-full wp-image-4535\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-08.png\" alt=\"Named Ranges with Vlookup Formula\" width=\"826\" height=\"222\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-08.png 826w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-08-300x81.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/09\/vlookup-named-range-08-768x206.png 768w\" sizes=\"(max-width: 826px) 100vw, 826px\" \/><\/a><\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-vs-vlookup\/\" target=\"_blank\" rel=\"noopener\">XLOOKUP vs VLOOKUP: Top 5 Comparison of Excel Lookup Functions<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/why-vlookup-not-working\/\" target=\"_blank\" rel=\"noopener\">Why VLOOKUP not working in Excel &#8211; Top 5 Problems with Solutions<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/\" target=\"_blank\" rel=\"noopener\">2 Easy Methods on How to Use XLOOKUP with Multiple Criteria<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) What it means: =VLOOKUP(this value, in this Named Range, and get me value in [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17056,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,11,276],"tags":[43,107,44],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1641"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1641"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1641\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17056"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}