{"id":295,"date":"2023-11-06T00:38:47","date_gmt":"2023-11-06T00:38:47","guid":{"rendered":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/"},"modified":"2023-12-25T10:33:20","modified_gmt":"2023-12-25T10:33:20","slug":"indirecto-con-vlookup-2","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/","title":{"rendered":"Indirecto con buscarv en excel"},"content":{"rendered":"<div class=\"excel-antes-del-contenido\" id=\"excel-4002248341\"><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>Si quieres utilizar BUSCARV y los datos que quieres buscar est\u00e1n en hojas diferentes, puedes combinarlos con INDIRECTO. Le ayuda a definir m\u00faltiples rangos en una sola f\u00f3rmula. <\/p>\n<div class=\"wp-block-image\">\n<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=\"indirecta-con-vlookup\" width=\"576\" height=\"501\"><\/figure>\n<\/div>\n<p> En el ejemplo anterior, tenemos datos mensuales en tres hojas de c\u00e1lculo diferentes. Pero con solo una BUSCARV + INDIRECTA puedes obtener la cantidad de todos los productos de todos los meses de varias hojas.<\/p>\n<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>\n<p> Para entender esta f\u00f3rmula, es necesario dividirla en dos partes:<\/p>\n<p> En la primera parte, tenemos la funci\u00f3n INDIRECTA, que crea una referencia a la hoja usando el nombre de la fila 1. En el siguiente ejemplo, hacemos referencia al rango A:B de Jan de la hoja. <\/p>\n<div class=\"wp-block-image\">\n<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-en-dos-partes\" width=\"706\" height=\"379\"><\/figure>\n<\/div>\n<p> Necesitas crear una estructura en INDIRECTO para referenciar la hoja con el nombre y rango donde tienes los datos.<\/p>\n<pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">=INDIRECT(\"'\"&amp;B$1&amp;\"'!\"&amp;\"A:B\")<\/code><\/pre>\n<p> Una vez que mueve la f\u00f3rmula a la columna de febrero, la referencia en INDIRECTA se mueve a la hoja de febrero. <\/p>\n<div class=\"wp-block-image\">\n<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=\"referencia-en-cambios-indirectos\" width=\"642\" height=\"265\"><\/figure>\n<\/div>\n<p> En la segunda parte, BUSCARV utiliza la direcci\u00f3n del rango de tabla devuelta por INDIRECT y obtiene sus valores en funci\u00f3n del col_index_num especificado en el rango.<\/p>\n<h2 class=\"wp-block-heading\"> Punto importante<\/h2>\n<p> En la f\u00f3rmula anterior, necesita la estructura correcta para hacer referencia a un rango con el nombre de la hoja. Si ingresas la siguiente estructura en INDIRECTO:<\/p>\n<pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">\"'\"&amp;B$1&amp;\"'!\"&amp;\"A:B\"<\/code><\/pre>\n<p> El volver\u00e1:<\/p>\n<pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">\"'Jan'!A:B\"<\/code><\/pre>\n<h2 class=\"wp-block-heading\"> M\u00e9todo alternativo<\/h2>\n<p> INDIRECTA es una funci\u00f3n vol\u00e1til. Se actualiza cuando hay un cambio en la hoja de c\u00e1lculo. Es por eso que puedes considerar usar ELEGIR. Por ejemplo, con ELEGIR puedes escribir tres f\u00f3rmulas usando BUSCARV. <\/p>\n<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>\n<div class=\"wp-block-image\">\n<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=\"elegir con vlookup\" width=\"752\" height=\"211\"><\/figure>\n<\/div>\n<p> En esta f\u00f3rmula, como dije, tenemos tres BUSCARV, y cuando con ELEGIR puedes decidir obtener el resultado de cualquiera de las BUSCARV <\/p>\n<div class=\"wp-block-image\">\n<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=\"con-elegir-obtener-resultado-de-cualquier-vlookup\" width=\"416\" height=\"157\"><\/figure>\n<\/div>\n<p> En la ELEGIR nos hemos referido al B1; En la l\u00ednea 1 tiene n\u00fameros de \u00edndice que puede usar para obtener el valor de la f\u00f3rmula de SELECT.<\/p>\n<p> Por ejemplo, cuando tenga 2, ELEGIR devolver\u00e1 el valor de la segunda BUSCARV; del tercero, hay 3. <\/p>\n<div class=\"wp-block-image\">\n<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-elegir-referencia\" width=\"588\" height=\"339\"><\/figure>\n<\/div>\n<div class=\"wp-block-group download-box is-layout-constrained\">\n<div class=\"wp-block-group__inner-container\">\n<h2 class=\"wp-block-heading\"> Descargar un archivo de muestra <\/h2>\n<ul id=\"ub_styled_list-e2b2c5eb-d85e-4189-8f42-829e40d51a0f\" class=\"ub_styled_list\">\n<li class=\"ub_styled_list_item\"> <a href=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/indirect-avec-vlookup.xlsx\" rel=\"nofollow\">Descargar<\/a><\/li>\n<\/ul>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Si quieres utilizar BUSCARV y los datos que quieres buscar est\u00e1n en hojas diferentes, puedes combinarlos con INDIRECTO. Le ayuda a definir m\u00faltiples rangos en una sola f\u00f3rmula. En el ejemplo anterior, tenemos datos mensuales en tres hojas de c\u00e1lculo diferentes. Pero con solo una BUSCARV + INDIRECTA puedes obtener la cantidad de todos los [&hellip;]<\/p>\n","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-295","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>Indirecto con buscarv en excel<\/title>\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\/es\/indirecto-con-vlookup-2\/\" \/>\n<meta property=\"og:locale\" content=\"es_ES\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Indirecto con buscarv en excel\" \/>\n<meta property=\"og:description\" content=\"Si quieres utilizar BUSCARV y los datos que quieres buscar est\u00e1n en hojas diferentes, puedes combinarlos con INDIRECTO. Le ayuda a definir m\u00faltiples rangos en una sola f\u00f3rmula. En el ejemplo anterior, tenemos datos mensuales en tres hojas de c\u00e1lculo diferentes. Pero con solo una BUSCARV + INDIRECTA puedes obtener la cantidad de todos los [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-25T10:33:20+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=\"Tiempo de lectura\" \/>\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\/es\/indirecto-con-vlookup-2\/\",\"url\":\"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/\",\"name\":\"Indirecto con buscarv en excel\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/es\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/es\/indirecto-con-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-25T10:33:20+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/#breadcrumb\"},\"inLanguage\":\"es\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"es\",\"@id\":\"https:\/\/exceladvisor.org\/es\/indirecto-con-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\/es\/indirecto-con-vlookup-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Hogar\",\"item\":\"https:\/\/exceladvisor.org\/es\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Indirecto con buscarv en excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/es\/#website\",\"url\":\"https:\/\/exceladvisor.org\/es\/\",\"name\":\"Excel Advisor\",\"description\":\"Su gu\u00eda para la dominaci\u00f3n de datos!\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/es\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/es\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"es\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/es\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/es\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"es\",\"@id\":\"https:\/\/exceladvisor.org\/es\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/es\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/es\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/es\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Indirecto con buscarv en excel","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\/es\/indirecto-con-vlookup-2\/","og_locale":"es_ES","og_type":"article","og_title":"Indirecto con buscarv en excel","og_description":"Si quieres utilizar BUSCARV y los datos que quieres buscar est\u00e1n en hojas diferentes, puedes combinarlos con INDIRECTO. Le ayuda a definir m\u00faltiples rangos en una sola f\u00f3rmula. En el ejemplo anterior, tenemos datos mensuales en tres hojas de c\u00e1lculo diferentes. Pero con solo una BUSCARV + INDIRECTA puedes obtener la cantidad de todos los [&hellip;]","og_url":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-25T10:33:20+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":{"Tiempo de lectura":"2 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/","url":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/","name":"Indirecto con buscarv en excel","isPartOf":{"@id":"https:\/\/exceladvisor.org\/es\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/es\/indirecto-con-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-25T10:33:20+00:00","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/#breadcrumb"},"inLanguage":"es","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/es\/indirecto-con-vlookup-2\/"]}]},{"@type":"ImageObject","inLanguage":"es","@id":"https:\/\/exceladvisor.org\/es\/indirecto-con-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\/es\/indirecto-con-vlookup-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Hogar","item":"https:\/\/exceladvisor.org\/es\/"},{"@type":"ListItem","position":2,"name":"Indirecto con buscarv en excel"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/es\/#website","url":"https:\/\/exceladvisor.org\/es\/","name":"Excel Advisor","description":"Su gu\u00eda para la dominaci\u00f3n de datos!","publisher":{"@id":"https:\/\/exceladvisor.org\/es\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/es\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"es"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/es\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/es\/","logo":{"@type":"ImageObject","inLanguage":"es","@id":"https:\/\/exceladvisor.org\/es\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/es\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/es\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/es\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/pages\/295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/comments?post=295"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/pages\/295\/revisions"}],"predecessor-version":[{"id":1471,"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/pages\/295\/revisions\/1471"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/es\/wp-json\/wp\/v2\/media?parent=295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}