{"id":1728,"date":"2023-11-08T22:51:33","date_gmt":"2023-11-08T22:51:33","guid":{"rendered":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/"},"modified":"2023-11-08T22:51:33","modified_gmt":"2023-11-08T22:51:33","slug":"xlookup-dengan-beberapa-kriteria","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/","title":{"rendered":"Xlookup dengan beberapa kriteria di excel"},"content":{"rendered":"<div class=\"excel-before-content\" id=\"excel-935110678\"><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 mencari menggunakan beberapa kriteria, Anda dapat menggunakan XLOOKUP. Dan di XLOOKUP ada dua cara untuk melakukannya. Dalam tutorial ini, kita akan melihat dan memahami kedua metode secara detail.<\/p><p> Pada contoh di bawah ini kita memiliki daftar siswa dengan nomor roll, nama dan nilai. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85860\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png\" sizes=\"auto, \" srcset=\"\" alt=\"xlookup-dengan-banyak-kriteria\" width=\"383\" height=\"407\"><\/figure><\/div><h2 class=\"wp-block-heading\"> Beberapa kriteria dengan XLOOKUP (metode kolom pembantu)<\/h2><ol><li> Pertama, tambahkan kolom baru untuk digunakan sebagai kolom pembantu. <br><img loading=\"lazy\" decoding=\"async\" title=\"2-tambahkan-kolom baru\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/2-ajouter-une-nouvelle-colonne.png\" sizes=\"auto, \" srcset=\"\" alt=\"add-new-column\" width=\"524\" height=\"238\"><\/li><li> Sekarang, di kolom wizard, Anda perlu menggabungkan nomor dan nama siswa. <br><img loading=\"lazy\" decoding=\"async\" title=\"3-gabungkan-data-ke dalam kolom-baru\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/3-combiner-les-donnees-dans-une-nouvelle-colonne.png\" sizes=\"auto, \" srcset=\"\" alt=\"combine-data-in-new-column\" width=\"523\" height=\"221\"><\/li><li> Setelah itu, buat nilai gabungan yang akan dicari. Kemudian, dalam dua sel, tulis nomor dan nama siswa. <br><img loading=\"lazy\" decoding=\"async\" title=\"4-buat-nilai gabungan\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/4-creer-une-valeur-concatenee.png\" sizes=\"auto, \" srcset=\"\" alt=\"create-concatenated-value\" width=\"665\" height=\"183\"><\/li><li> Selanjutnya, masukkan XLOOKUP dan gabungkan nilai dari panggilan F2 dan G2 untuk digunakan sebagai nilai pencarian dalam fungsi tersebut. <br><img loading=\"lazy\" decoding=\"async\" title=\"5-xlookup-dengan-nilai gabungan\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/5-xlookup-avec-des-valeurs-combinees.png\" sizes=\"auto, \" srcset=\"\" alt=\"xlookup-with-combined-values\" width=\"428\" height=\"97\"><\/li><li> Dari sana, rujuk kolom bantuan sebagai array_pencarian dan kolom skor sebagai array_pengembalian. <br><img loading=\"lazy\" decoding=\"async\" title=\"6-rujuk-ke-kolom-bantuan-sebagai-tabel-pencarian\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/6-se-referer-a-la-colonne-dassistance-en-tant-que-tableau-de-recherche.png\" sizes=\"auto, \" srcset=\"\" alt=\"refer-to-helper-column-as-lookup-array\" width=\"693\" height=\"353\"><\/li><li> Pada akhirnya, tekan Enter untuk mendapatkan hasilnya. <br><img loading=\"lazy\" decoding=\"async\" title=\"7-xlookup-hasil-dengan-banyak-kriteria\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/7-resultat-xlookup-avec-plusieurs-criteres.png\" sizes=\"auto, \" srcset=\"\" alt=\"xlookup-result-with-multiple-criteria\" width=\"673\" height=\"266\"><\/li><\/ol><p> Anda dapat menggunakan argumen opsional lainnya di XLOOKUP jika Anda mau.<\/p><h2 class=\"wp-block-heading\"> Beberapa kriteria dengan XLOOKUP (metode array)<\/h2><p> Anda bisa menggunakan tabel jika tidak ingin menggunakan kolom pembantu. Mari kita mulai dengan menulis rumus ini, lalu pelajari cara kerjanya.<\/p><ol><li> Masukkan XLOOKUP di sel dan di lookup_value masukkan 1. <br><img loading=\"lazy\" decoding=\"async\" title=\"8-xlookup-array-metode\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/8-methode-de-tableau-xlookup.png\" sizes=\"auto, \" srcset=\"\" alt=\"xlookup-array-method\" width=\"673\" height=\"258\"><\/li><li> Sekarang kita perlu membuat tabel untuk mencari nomor roll pada kolom nomor roll dan nama pada kolom nama. Jadi, masukkan (A2:A20=E2)*(B2:B20=F2) di tabel pencarian. <br><img loading=\"lazy\" decoding=\"async\" title=\"9-buat-papan\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/9-creer-un-tableau.png\" sizes=\"auto, \" srcset=\"\" alt=\"create-an-array\" width=\"630\" height=\"119\"><\/li><li> Setelah itu pada return_array lihat kolom score. <br><img loading=\"lazy\" decoding=\"async\" title=\"10-in-return-array-rujuk-kolom lain\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/10-dans-le-tableau-de-retour-renvoient-a-une-autre-colonne.png\" sizes=\"auto, \" srcset=\"\" alt=\"in-return-array-refer-another-column\" width=\"694\" height=\"91\"><\/li><li> Pada akhirnya, tutup fungsi dan tekan Enter untuk mendapatkan hasilnya. <br><img loading=\"lazy\" decoding=\"async\" title=\"11-dapatkan-hasilnya\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/11-obtenir-le-resultat.png\" sizes=\"auto, \" srcset=\"\" alt=\"get-the-result\" width=\"632\" height=\"383\"><\/li><\/ol><p> Tabel inilah yang membuat formula ini ampuh. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85871\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/12-tableaux-rendent-la-formule-puissante.png\" sizes=\"auto, \" srcset=\"\" alt=\"tabel-membuat-rumus-kuat\" width=\"643\" height=\"71\"><\/figure><\/div><p> Mari kita pahami ini secara detail.<\/p><p> Di bagian pertama tabel, kami menguji nama di kolom nama, dan mengembalikan array dengan TRUE dan FALSE. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85872\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/13-tableau-renvoie-vrai-ou-faux.png\" sizes=\"auto, \" srcset=\"\" alt=\"array-mengembalikan-benar-atau-salah\" width=\"565\" height=\"227\"><\/figure><\/div><p> Terlihat ada TRUE pada posisi keenam tabel tersebut, yaitu posisi nama yang kita cari pada kolom nama. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85873\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/14-vrai-fait-reference-a-la-position-de-la-valeur-referencee.png\" sizes=\"auto, \" srcset=\"\" alt=\"benar-merujuk-ke-posisi-nilai-yang-direferensikan\" width=\"684\" height=\"355\"><\/figure><\/div><p> Hal yang sama juga terjadi pada tabel kedua, dimana kita mempunyai TRUE untuk nomor peran yang kita cari. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85874\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/15-vrai-pour-une-autre-valeur-de-recherche.png\" sizes=\"auto, \" srcset=\"\" alt=\"benar-untuk-nilai-pencarian-lainnya\" width=\"642\" height=\"375\"><\/figure><\/div><p> Selain itu, saat Anda mengalikan kedua larik, Anda mendapatkan larik baru dengan 0 dan 1. Dalam larik ini, 1 berada di posisi keenam. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-85875\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/16-multiplier-les-deux-tableaux.png\" sizes=\"auto, \" srcset=\"\" alt=\"kalikan kedua array\" width=\"700\" height=\"63\"><\/figure><\/div><p> Kami memiliki fungsi lookup_value 1, dan array di lookup_array 1 berada di posisi keenam. Inilah sebabnya XLOOKUP mengembalikan skor dari posisi keenam. <\/p><div class=\"gb-container gb-container-52dfcee1 gb-container-sample-file\"><h2 class=\"gb-headline gb-headline-e289c143 gb-headline-text gb-headline-4f7959c2\"> Dapatkan file Excelnya<\/h2> <a class=\"gb-button gb-button-e88d6bee gb-button-text gb-button-button1\" href=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/xlookup-plusieurs-criteres-2.xlsx\" rel=\"nofollow\">Unduh<\/a><\/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-1728","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>XLOOKUP dengan berbagai kriteria di Excel \u2013 Excel Advisor<\/title>\n<meta name=\"description\" content=\"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel menggunakan XLOOKUP dengan beberapa kriteria. Mari kita periksa ini...\" \/>\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\/xlookup-dengan-beberapa-kriteria\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"XLOOKUP dengan berbagai kriteria di Excel \u2013 Excel Advisor\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel menggunakan XLOOKUP dengan beberapa kriteria. Mari kita periksa ini...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.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\/xlookup-dengan-beberapa-kriteria\/\",\"url\":\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/\",\"name\":\"XLOOKUP dengan berbagai kriteria di Excel \u2013 Excel Advisor\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png\",\"datePublished\":\"2023-11-08T22:51:33+00:00\",\"description\":\"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel menggunakan XLOOKUP dengan beberapa kriteria. Mari kita periksa ini...\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Rumah\",\"item\":\"https:\/\/exceladvisor.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Xlookup dengan beberapa kriteria 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":"XLOOKUP dengan berbagai kriteria di Excel \u2013 Excel Advisor","description":"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel menggunakan XLOOKUP dengan beberapa kriteria. Mari kita periksa ini...","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\/xlookup-dengan-beberapa-kriteria\/","og_locale":"id_ID","og_type":"article","og_title":"XLOOKUP dengan berbagai kriteria di Excel \u2013 Excel Advisor","og_description":"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel menggunakan XLOOKUP dengan beberapa kriteria. Mari kita periksa ini...","og_url":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/","og_site_name":"Excel Advisor","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.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\/xlookup-dengan-beberapa-kriteria\/","url":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/","name":"XLOOKUP dengan berbagai kriteria di Excel \u2013 Excel Advisor","isPartOf":{"@id":"https:\/\/exceladvisor.org\/id\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png","datePublished":"2023-11-08T22:51:33+00:00","description":"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel menggunakan XLOOKUP dengan beberapa kriteria. Mari kita periksa ini...","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/"]}]},{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-xlookup-avec-plusieurs-criteres.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/id\/xlookup-dengan-beberapa-kriteria\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Rumah","item":"https:\/\/exceladvisor.org\/id\/"},{"@type":"ListItem","position":2,"name":"Xlookup dengan beberapa kriteria 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\/1728","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=1728"}],"version-history":[{"count":0,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages\/1728\/revisions"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/media?parent=1728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}