{"id":1769,"date":"2023-11-06T00:38:47","date_gmt":"2023-11-06T00:38:47","guid":{"rendered":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/"},"modified":"2023-11-06T00:38:47","modified_gmt":"2023-11-06T00:38:47","slug":"tidak-langsung-dengan-vlookup-2","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/","title":{"rendered":"Tidak langsung dengan vlookup di excel"},"content":{"rendered":"<div class=\"excel-before-content\" id=\"excel-2754652071\"><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>Jika Anda ingin menggunakan VLOOKUP dan data yang ingin dicari berada pada sheet yang berbeda, Anda dapat menggabungkannya dengan INDIRECT. Ini membantu Anda menentukan beberapa rentang dalam satu rumus. <\/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=\"tidak langsung-dengan-vlookup\" width=\"576\" height=\"501\"><\/figure><\/div><p> Pada contoh di atas, kami memiliki data bulanan dalam tiga spreadsheet berbeda. Namun hanya dengan satu VLOOKUP + INDIRECT Anda bisa mendapatkan jumlah semua produk sepanjang bulan dari beberapa lembar.<\/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> Untuk memahami rumus ini, Anda perlu membaginya menjadi dua bagian:<\/p><p> Pada bagian pertama, kita memiliki fungsi INDIRECT, yang membuat referensi ke sheet menggunakan nama baris 1. Pada contoh di bawah, kita mereferensikan rentang sheet Jan A:B. <\/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=\"rumus-dibagi-menjadi-dua-bagian\" width=\"706\" height=\"379\"><\/figure><\/div><p> Anda perlu membuat struktur di INDIRECT untuk mereferensikan sheet dengan nama dan rentang tempat Anda memiliki datanya.<\/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> Setelah Anda memindahkan rumus ke kolom Feb, referensi di INDIRECT berpindah ke lembar Feb. <\/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=\"referensi-dalam-perubahan-tidak langsung\" width=\"642\" height=\"265\"><\/figure><\/div><p> Pada bagian kedua, VLOOKUP menggunakan alamat rentang tabel yang dikembalikan oleh INDIRECT dan mendapatkan nilainya berdasarkan col_index_num yang ditentukan dalam rentang tersebut.<\/p><h2 class=\"wp-block-heading\"> Poin penting<\/h2><p> Dalam rumus di atas, Anda memerlukan struktur yang benar untuk mereferensikan rentang dengan nama sheet. Jika Anda memasukkan struktur di bawah ini secara TIDAK LANGSUNG:<\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">\"'\"&amp;B$1&amp;\"'!\"&amp;\"A:B\"<\/code><\/pre><p> Dia akan kembali:<\/p><pre class=\"wp-block-code\"> <code class=\"language-excel-formula\" lang=\"excel-formula\">\"'Jan'!A:B\"<\/code><\/pre><h2 class=\"wp-block-heading\"> Metode alternatif<\/h2><p> TIDAK LANGSUNG adalah fungsi yang mudah menguap. Ini diperbarui ketika ada perubahan pada spreadsheet. Inilah mengapa Anda dapat mempertimbangkan untuk menggunakan CHOOSE. Misalnya, dengan CHOOSE Anda bisa menulis tiga rumus menggunakan 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=\"pilih dengan vlookup\" width=\"752\" height=\"211\"><\/figure><\/div><p> Dalam rumus ini, seperti yang saya katakan, kita memiliki tiga VLOOKUP, dan ketika dengan CHOOSE Anda dapat memutuskan untuk mendapatkan hasil dari salah satu VLOOKUP tersebut <\/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=\"dengan-pilih-dapatkan-hasil-dari-vlookup mana pun\" width=\"416\" height=\"157\"><\/figure><\/div><p> Dalam CHOOSE kita mengacu pada B1; Pada baris 1 Anda memiliki nomor indeks yang akan digunakan untuk mendapatkan nilai rumus dari SELECT.<\/p><p> Misalnya, jika Anda memiliki 2, CHOOSE akan mengembalikan nilai VLOOKUP kedua; dari yang ketiga ada 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-nilai-dengan-pilih-referensi\" 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\"> Unduh file contoh <\/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\">Unduh<\/a><\/li><\/ul><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-1769","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>Tidak langsung dengan VLOOKUP di Excel \u2013 Excel Advisor<\/title>\n<meta name=\"description\" content=\"Tutorial ini akan membantu Anda mempelajari cara menggunakan INDIRECT dengan VLOOKUP ketika Anda ingin mencari di berbagai sheet.\" \/>\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\/id\/tidak-langsung-dengan-vlookup-2\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tidak langsung dengan VLOOKUP di Excel \u2013 Excel Advisor\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini akan membantu Anda mempelajari cara menggunakan INDIRECT dengan VLOOKUP ketika Anda ingin mencari di berbagai sheet.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\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=\"Estimasi waktu membaca\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/\",\"url\":\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/\",\"name\":\"Tidak langsung dengan VLOOKUP di Excel \u2013 Excel Advisor\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-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\",\"description\":\"Tutorial ini akan membantu Anda mempelajari cara menggunakan INDIRECT dengan VLOOKUP ketika Anda ingin mencari di berbagai sheet.\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-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\/id\/tidak-langsung-dengan-vlookup-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Rumah\",\"item\":\"https:\/\/exceladvisor.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Tidak langsung dengan vlookup di excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/id\/#website\",\"url\":\"https:\/\/exceladvisor.org\/id\/\",\"name\":\"Excel Advisor\",\"description\":\"Panduan utama Anda untuk mendominasi data!\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/id\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"id\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/id\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/id\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Tidak langsung dengan VLOOKUP di Excel \u2013 Excel Advisor","description":"Tutorial ini akan membantu Anda mempelajari cara menggunakan INDIRECT dengan VLOOKUP ketika Anda ingin mencari di berbagai sheet.","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\/id\/tidak-langsung-dengan-vlookup-2\/","og_locale":"id_ID","og_type":"article","og_title":"Tidak langsung dengan VLOOKUP di Excel \u2013 Excel Advisor","og_description":"Tutorial ini akan membantu Anda mempelajari cara menggunakan INDIRECT dengan VLOOKUP ketika Anda ingin mencari di berbagai sheet.","og_url":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/","og_site_name":"Excel Advisor","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":{"Estimasi waktu membaca":"2 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/","url":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/","name":"Tidak langsung dengan VLOOKUP di Excel \u2013 Excel Advisor","isPartOf":{"@id":"https:\/\/exceladvisor.org\/id\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-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","description":"Tutorial ini akan membantu Anda mempelajari cara menggunakan INDIRECT dengan VLOOKUP ketika Anda ingin mencari di berbagai sheet.","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-vlookup-2\/"]}]},{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/exceladvisor.org\/id\/tidak-langsung-dengan-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\/id\/tidak-langsung-dengan-vlookup-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Rumah","item":"https:\/\/exceladvisor.org\/id\/"},{"@type":"ListItem","position":2,"name":"Tidak langsung dengan vlookup di excel"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/id\/#website","url":"https:\/\/exceladvisor.org\/id\/","name":"Excel Advisor","description":"Panduan utama Anda untuk mendominasi data!","publisher":{"@id":"https:\/\/exceladvisor.org\/id\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/id\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"id"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/id\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/id\/","logo":{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages\/1769","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/comments?post=1769"}],"version-history":[{"count":0,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages\/1769\/revisions"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/media?parent=1769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}