{"id":294,"date":"2023-11-06T00:38:47","date_gmt":"2023-11-06T00:38:47","guid":{"rendered":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/"},"modified":"2023-12-06T11:50:59","modified_gmt":"2023-12-06T11:50:59","slug":"indireto-com-vlookup-2","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/","title":{"rendered":"Indireto com vlookup no excel"},"content":{"rendered":"<div class=\"excel-before-content-2\" id=\"excel-2014037428\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div><div class=\"excel-before-content\" id=\"excel-3223967785\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div> <p>Se voc\u00ea quiser usar VLOOKUP e os dados que deseja pesquisar estiverem em planilhas diferentes, voc\u00ea pode combin\u00e1-los com INDIRETO. Ajuda a definir v\u00e1rios intervalos em uma \u00fanica f\u00f3rmula. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85926\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png\" sizes=\"auto, \" srcset=\"\" alt=\"indireto com vlookup\" width=\"576\" height=\"501\"><\/figure><\/div><p> No exemplo acima, temos dados mensais em tr\u00eas planilhas diferentes. Mas com apenas um VLOOKUP + INDIRETO voc\u00ea consegue obter a quantidade de todos os produtos de todos os meses em v\u00e1rias planilhas.<\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">=VLOOKUP($A2,INDIRECT(\"'\"&amp;B$1&amp;\"'!\"&amp;\"A:B\"),2,FALSE)<\/code><\/pre><p> Para entender esta f\u00f3rmula, \u00e9 necess\u00e1rio dividi-la em duas partes:<\/p><p> Na primeira parte, temos a fun\u00e7\u00e3o INDIRETO, que cria uma refer\u00eancia para a planilha utilizando o nome da linha 1. No exemplo abaixo, referenciamos o intervalo A:B da planilha Jan. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85927\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/2-formule-divisee-en-deux-parties.png\" sizes=\"auto, \" srcset=\"\" alt=\"f\u00f3rmula dividida em duas partes\" width=\"706\" height=\"379\"><\/figure><\/div><p> Voc\u00ea precisa criar uma estrutura em INDIRETO para referenciar a planilha com o nome e intervalo onde est\u00e3o os dados.<\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">=INDIRECT(\"'\"&amp;B$1&amp;\"'!\"&amp;\"A:B\")<\/code><\/pre><p> Depois de mover a f\u00f3rmula para a coluna Fev, a refer\u00eancia em INDIRETO passa para a planilha Fev. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85928\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/3-reference-dans-les-changements-indirects.png\" sizes=\"auto, \" srcset=\"\" alt=\"refer\u00eancia-em-mudan\u00e7as-indiretas\" width=\"642\" height=\"265\"><\/figure><\/div><p> Na segunda parte, VLOOKUP usa o endere\u00e7o do intervalo da tabela retornado por INDIRETO e obt\u00e9m seus valores com base no col_index_num especificado no intervalo.<\/p><h2 class=\"wp-block-heading\"> Ponto importante<\/h2><p> Na f\u00f3rmula acima, voc\u00ea precisa da estrutura correta para fazer refer\u00eancia a um intervalo com o nome da planilha. Se voc\u00ea inserir a estrutura abaixo em INDIRETO:<\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">\"'\"&amp;B$1&amp;\"'!\"&amp;\"A:B\"<\/code><\/pre><p> Ele vai voltar:<\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">\"'Jan'!A:B\"<\/code><\/pre><h2 class=\"wp-block-heading\"> M\u00e9todo alternativo<\/h2><p> INDIRETO \u00e9 uma fun\u00e7\u00e3o vol\u00e1til. Ele \u00e9 atualizado quando h\u00e1 uma altera\u00e7\u00e3o na planilha. \u00c9 por isso que voc\u00ea pode considerar usar CHOOSE. Por exemplo, com CHOOSE voc\u00ea pode escrever tr\u00eas f\u00f3rmulas usando VLOOKUP. <\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">=CHOOSE(B$1,VLOOKUP($A2,Jan!$A:$B,2,0),VLOOKUP($A2,Feb!$A:$B,2,0),VLOOKUP($A2,Mar!$A:$B,2,0))<\/code><\/pre><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85929\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/4-choisir-avec-vlookup.png\" sizes=\"auto, \" srcset=\"\" alt=\"escolha com vlookup\" width=\"752\" height=\"211\"><\/figure><\/div><p> Nessa f\u00f3rmula, como falei, temos tr\u00eas VLOOKUPs, e quando com o ESCOLHER voc\u00ea pode decidir obter o resultado de qualquer uma das VLOOKUPs <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85930\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/5-avec-choisir-obtenir-le-resultat-de-nimporte-quel-vlookup.png\" sizes=\"auto, \" srcset=\"\" alt=\"com-escolha-obter-resultado-de-qualquer-vlookup\" width=\"416\" height=\"157\"><\/figure><\/div><p> No ESCOLHER nos referimos ao B1; Na linha 1 voc\u00ea tem n\u00fameros de \u00edndice para usar para obter o valor da f\u00f3rmula de SELECT.<\/p><p> Por exemplo, quando voc\u00ea tiver 2, CHOOSE retornar\u00e1 o valor do segundo VLOOKUP; do terceiro, existem 3. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85931\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/6-valeurs-vlookup-par-reference-choisie.png\" sizes=\"auto, \" srcset=\"\" alt=\"vlookup-valor-por-escolha-refer\u00eancia\" width=\"588\" height=\"339\"><\/figure><\/div><div class=\"wp-block-group download-box is-layout-constrained\"><div class=\"wp-block-group__inner-container\"><h2 class=\"wp-block-heading\"> Baixe um arquivo de amostra <\/h2><ul id=\"ub_styled_list-e2b2c5eb-d85e-4189-8f42-829e40d51a0f\" class=\"ub_styled_list\"><li class=\"ub_styled_list_item\"> <a href=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/indirect-avec-vlookup.xlsx\" rel=\"nofollow\">Download<\/a><\/li><\/ul><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-294","page","type-page","status-publish","hentry"],"featured_image_src":null,"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Indireto com VLOOKUP no Excel \u2013 Excel Advisor<\/title>\n<meta name=\"description\" content=\"Este tutorial ir\u00e1 ajud\u00e1-lo a aprender como usar INDIRETO com VLOOKUP quando desejar pesquisar em planilhas diferentes.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/\" \/>\n<meta property=\"og:locale\" content=\"pt_PT\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Indireto com VLOOKUP no Excel \u2013 Excel Advisor\" \/>\n<meta property=\"og:description\" content=\"Este tutorial ir\u00e1 ajud\u00e1-lo a aprender como usar INDIRETO com VLOOKUP quando desejar pesquisar em planilhas diferentes.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-06T11:50:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Tempo estimado de leitura\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/\",\"url\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/\",\"name\":\"Indireto com VLOOKUP no Excel \u2013 Excel Advisor\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/pt\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png\",\"datePublished\":\"2023-11-06T00:38:47+00:00\",\"dateModified\":\"2023-12-06T11:50:59+00:00\",\"description\":\"Este tutorial ir\u00e1 ajud\u00e1-lo a aprender como usar INDIRETO com VLOOKUP quando desejar pesquisar em planilhas diferentes.\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#breadcrumb\"},\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Lar\",\"item\":\"https:\/\/exceladvisor.org\/pt\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Indireto com vlookup no excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/pt\/#website\",\"url\":\"https:\/\/exceladvisor.org\/pt\/\",\"name\":\"Excel Advisor\",\"description\":\"O seu guia definitivo para dominar os dados!\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/pt\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/pt\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-PT\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/pt\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/pt\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\/\/exceladvisor.org\/pt\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/pt\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/pt\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/pt\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Indireto com VLOOKUP no Excel \u2013 Excel Advisor","description":"Este tutorial ir\u00e1 ajud\u00e1-lo a aprender como usar INDIRETO com VLOOKUP quando desejar pesquisar em planilhas diferentes.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/","og_locale":"pt_PT","og_type":"article","og_title":"Indireto com VLOOKUP no Excel \u2013 Excel Advisor","og_description":"Este tutorial ir\u00e1 ajud\u00e1-lo a aprender como usar INDIRETO com VLOOKUP quando desejar pesquisar em planilhas diferentes.","og_url":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-06T11:50:59+00:00","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Tempo estimado de leitura":"2 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/","url":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/","name":"Indireto com VLOOKUP no Excel \u2013 Excel Advisor","isPartOf":{"@id":"https:\/\/exceladvisor.org\/pt\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png","datePublished":"2023-11-06T00:38:47+00:00","dateModified":"2023-12-06T11:50:59+00:00","description":"Este tutorial ir\u00e1 ajud\u00e1-lo a aprender como usar INDIRETO com VLOOKUP quando desejar pesquisar em planilhas diferentes.","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#breadcrumb"},"inLanguage":"pt-PT","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/"]}]},{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-indirect-avec-vlookup.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/pt\/indireto-com-vlookup-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Lar","item":"https:\/\/exceladvisor.org\/pt\/"},{"@type":"ListItem","position":2,"name":"Indireto com vlookup no excel"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/pt\/#website","url":"https:\/\/exceladvisor.org\/pt\/","name":"Excel Advisor","description":"O seu guia definitivo para dominar os dados!","publisher":{"@id":"https:\/\/exceladvisor.org\/pt\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/pt\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-PT"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/pt\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/pt\/","logo":{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/exceladvisor.org\/pt\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/pt\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/pt\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/pt\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/pages\/294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/comments?post=294"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/pages\/294\/revisions"}],"predecessor-version":[{"id":1437,"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/pages\/294\/revisions\/1437"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/pt\/wp-json\/wp\/v2\/media?parent=294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}