Tutorial ini akan mendemonstrasikan cara melakukan VLOOKUP pada banyak sheet di Excel dan Google Sheets. Jika versi Excel Anda mendukung XLOOKUP, sebaiknya gunakan XLOOKUP sebagai gantinya Show
Fungsi VLOOKUP hanya dapat melakukan pencarian pada satu kumpulan data. Jika kita ingin melakukan pencarian di antara beberapa set data yang disimpan di lembar berbeda, kita dapat menggabungkan semua set data menjadi satu set atau menggunakan Fungsi IFERROR bersama dengan VLOOKUP untuk melakukan beberapa pencarian dalam satu formula Metode yang terakhir lebih sederhana di Excel sedangkan yang pertama lebih sederhana di Google Sheets
VLOOKUP dengan IFERRORFungsi VLOOKUP (dan rumus pencarian lainnya) mengembalikan kesalahan jika tidak dapat menemukan kecocokan, dan kami biasanya menggunakan Fungsi IFERROR untuk mengganti kesalahan dengan nilai yang disesuaikan Alih-alih nilai yang disesuaikan, kami akan menggunakan IFERROR untuk melakukan VLOOKUP lain dari sheet lain jika VLOOKUP pertama tidak dapat menemukan kecocokan di sheet pertama VLOOKUP – 2 Lembar Sekaligus
Mari telusuri rumusnya
Fungsi VLOOKUPInilah VLOOKUP pertama _Catatan. Fungsi VLOOKUP mencari nilai pencarian dari kolom pertama tabel dan mengembalikan nilai terkait dari kolom yang ditentukan oleh indeks kolom (i. e. , argumen ke-3). Argumen terakhir mendefinisikan jenis kecocokan (i. e. , Benar – Pencocokan Perkiraan, Salah – Pencocokan Persis) Inilah VLOOKUP untuk Lembar ke-2
Fungsi IFERRORKami menggabungkan hasil dari 2 VLOOKUP menggunakan Fungsi IFERROR Kami memeriksa nilai VLOOKUP pertama (mis. g. , Departemen A). Jika nilai adalah kesalahan, nilai yang sesuai dari VLOOKUP ke-2 akan dikembalikan
Catatan. Fungsi IFERROR memeriksa nilai (mis. e. , argumen ke-1) jika itu adalah kesalahan (mis. g. , #T/A, #REF. ). Jika itu adalah kesalahan, maka itu akan mengembalikan nilai dari argumen terakhirnya, bukan kesalahannya. Jika itu bukan kesalahan, itu akan mengembalikan nilai yang sedang diperiksa
Menggabungkan semua hasil konsep ini ke formula asli kami
VLOOKUP – Lebih dari 2 Lembar SekaligusUntuk lebih dari dua lembar, kita perlu menambahkan satu VLOOKUP dan satu IFERROR per lembar tambahan ke rumus di atas. Berikut rumus untuk 3 lembar
VLOOKUP Beberapa Lembar Sekaligus di Google LembarMeskipun kombinasi IFERROR dan VLOOKUP bekerja dengan cara yang sama di Google Sheets, lebih mudah menggabungkan kumpulan data menjadi satu
VLOOKUP dengan Kurung Keriting {} (Google Spreadsheet)Kita dapat menggabungkan kumpulan data menjadi satu menggunakan kurung kurawal _Mari uraikan dan visualisasikan rumusnya Tumpuk semua kumpulan data secara vertikal dengan memisahkan rentang sel menggunakan titik koma di dalam tanda kurung kurawal
Catatan. Tanda kurung kurawal di Google Sheets juga dapat digunakan untuk membuat larik dari rentang sel. Titik koma digunakan untuk menumpuk nilai secara vertikal sedangkan koma digunakan untuk menumpuk nilai secara horizontal. Jika rentang sel akan ditumpuk secara vertikal, kita perlu memastikan no. kolom untuk setiap rentang sel adalah sama. Jika tidak, itu tidak akan berhasil. Untuk horizontal, no. dari baris harus sama Hari ini kami akan menunjukkan cara menggunakan rumus VLOOKUP dengan banyak lembar di Excel. Sebenarnya, salah satu fungsi Excel yang paling penting dan banyak digunakan adalah fungsi VLOOKUP. Selanjutnya, kita dapat menggunakan fungsi VBA VLOOKUP untuk mencari data tertentu dalam satu lembar kerja, atau dalam rentang lembar kerja Selain itu, hari ini kami akan menunjukkan bagaimana kami dapat menggunakan rumus VLOOKUP untuk mencari beberapa data spesifik di beberapa lembar kerja di Excel Daftar isi Download Buku LatihanAnda dapat mengunduh buku latihan dari sini Rumus VLOOKUP dengan Beberapa Lembar. xlsx Tanda Ujian Palsu. xlsx Pengantar Fungsi VLOOKUP Excel=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Kemudian, kembalikan nilai dari sel itu Selain itu, kami telah melampirkan contoh fungsi VLOOKUP ini. Sekarang, lihat gambar berikutPenguraian Formula Di sini, rumus VLOOKUP(G8,B4. D15,3,FALSE) mencari nilai sel G8 “Angela” di kolom pertama tabel. B4. D15 Setelah menemukan satu, ia pindah ke kolom ke-3 (Karena col_index_number adalah 3. ) Kemudian mengembalikan nilai dari sana, adalah 322 5 Cara Menggunakan Rumus VLOOKUP di Excel dengan Banyak LembarDi sini, kami memiliki buku kerja dengan nilai ujian tertulis dan viva beberapa kandidat dalam tiga minggu di lembar kerja yang berbeda. Selain itu, nama yang pertama adalah Minggu 1 Kemudian, nama lembar kerja ke-2 adalah Minggu ke-2 Terakhir, nama lembar kerja ke-3 yang berisi tanda-tanda Marco Group adalah Minggu ke-3 Sekarang, tujuan kita adalah mengekstrak nilai mereka dari tiga lembar kerja ke lembar kerja baru menggunakan fungsi VLOOKUP dari Excel 1. Rumus VLOOKUP untuk Mencari di Setiap Lembar Kerja Secara TerpisahDi sini, kami memiliki lembar kerja baru yang disebut "hanya VLOOKUP" dengan nama semua kandidat yang diurutkan menurut abjad (A hingga Z). Sekarang, kita akan menggunakan rumus VLOOKUP untuk mencari dari beberapa sheet di Excel Pertama-tama, kita akan mencari melalui tiga lembar kerja secara terpisah Di sini kita akan mencari lookup_value dari satu lembar kerja ke dalam rentang sel lembar kerja lain Sintaks rumusnya adalah =VLOOKUP(lookup_value,‘Sheet_name’. table_array, col_index_number, FALSE)
Ini menunjukkan #N/A. Error, karena nilai sel B5 di lembar “VLOOKUP only”, Alex Hales, tidak ada di rentang B5. D10 dari lembar “Minggu 1“
Akibatnya, kami melihat tanda hanya kandidat yang muncul di Minggu 1 yang ditampilkan, sisanya menunjukkan kesalahan
Jadi, kami melihat tanda-tanda hanya kandidat yang muncul di Minggu 1 yang ditampilkan, sisanya menunjukkan kesalahan Selain itu, kita juga dapat melakukan tugas serupa untuk Minggu 2 dan Minggu 3, tetapi itu tidak akan memuaskan kebutuhan kita. Oleh karena itu, kita harus mencari pendekatan yang lebih baik Melihat. Di sini, kami telah menggunakan referensi sel relatif untuk lookup_value (B5), tetapi referensi sel absolut untuk table_array ($B$5. $D$10). Karena kami ingin lookup_value bertambah satu per satu selama menyeret ikon Fill Handle, tetapi table_array tetap konstan Baca Selengkapnya. Mengapa VLOOKUP Mengembalikan #N/A Saat Ada Kecocokan? 2. Cari di Beberapa Lembar dengan Fungsi IFERROR di ExcelKali ini kita akan mencari kandidat terlebih dahulu di lembar kerja pertama (Minggu 1) Kemudian, jika kita tidak menemukannya di lembar kerja pertama, kita akan mencarinya di lembar kerja kedua (Minggu ke-2) Dan jika kita masih tidak menemukannya, kita akan mencarinya di lembar kerja ketiga (Minggu ke-3) Jika kami tidak menemukannya, kami akan memutuskan bahwa dia tidak hadir dalam ujian Di bagian sebelumnya kita melihat, VLOOKUP mengembalikan N/A. Kesalahan jika tidak menemukan kecocokan dengan lookup_value di table_array Jadi kali ini kita akan menyarangkan fungsi VLOOKUP di dalam fungsi IFERROR untuk menangani kesalahan Oleh karena itu sintaks rumusnya adalah =IFERROR(VLOOKUP(lookup_value,”Sheet1_Name”. table_array,col_index_number,FALSE),IFERROR(VLOOKUP(lookup_value,”Sheet2_Name”. table_array,col_index_number,FALSE),IFERROR(VLOOKUP(lookup_value,”Sheet3_Name”. table_array,col_index_number,FALSE),”Tidak Ada”)))
Hasilnya, Anda akan melihat tanda tertulis dari Alex Hales Kemudian, kita akan menemukan tanda viva dari Alex Hales
Terakhir, Anda akan melihat tanda tertulis dan viva untuk semua kandidat Baca Selengkapnya. Contoh VLOOKUP Antara Dua Lembar di Excel Bacaan Serupa
3. Menggunakan Formula Gabungan untuk Mencari di Beberapa Lembar di ExcelSebenarnya rumus nested IFERROR dan VLOOKUP yang kita gunakan tadi cukup membantu, namun masih sedikit rumit untuk digunakan. Pada dasarnya, ada kemungkinan besar untuk bingung dan menghasilkan kesalahan jika ada banyak lembar kerja Oleh karena itu, kita akan membuat rumus lain dengan menggunakan fungsi INDIRECT, INDEX, MATCH, dan COUNTIF yang terlihat lebih rumit, tetapi relatif lebih mudah diterapkan ketika lembar kerja banyak
Penguraian Formula
Di sini, Anda bisa menggunakan rumus serupa untuk mengetahui tanda Viva para kandidat
Terakhir, kami mendapatkan tanda tertulis dan viva dari semua kandidat. Apalagi yang namanya belum ditemukan sudah ditandai absen Baca Selengkapnya. INDEX MATCH vs Fungsi VLOOKUP (9 Contoh) 4. Rumus VLOOKUP dengan Nomor Indeks Kolom DinamisSampai saat ini, untuk mengekstrak nilai ujian tertulis, kami menggunakan col_index_num sebagai 2. Dan untuk viva mark 3 Sebenarnya, kami memasukkan rumus secara terpisah di kedua kolom Akhirnya, ketika kita memiliki beberapa kolom, akan cukup merepotkan untuk menyisipkan rumus di semua kolom secara terpisah Nah, kali ini kita akan generate sebuah formula sehingga kita bisa menyisipkan formula tersebut pada kolom pertama, dan drag ke semua kolom melalui icon Fill Handle Sederhana. Alih-alih memasukkan angka murni sebagai col_index_num, masukkan COLUMNS($C$1. D1) jika rumusnya ada di kolom C (Untuk Tanda Tertulis) Kemudian, itu akan mengembalikan 2 Kemudian, jika kita seret ke kolom E, itu akan menjadi COLUMNS($C$1. E1) dan kembali 3. Dan seterusnya
Terakhir, Anda akan melihat tanda tertulis dan viva untuk semua kandidat Melihat. Di sini, kami telah mengubah lookup_value dari B5 menjadi $B5, sehingga tetap tidak berubah saat diseret ke kanan, tetapi berubah saat diseret ke bawah Baca Selengkapnya. Excel Dynamic VLOOKUP (dengan 3 Rumus) 5. Rumus VLOOKUP dengan Fungsi Gabungan di ExcelDi sini, kita akan menggunakan rumus VLOOKUP lain di Excel dengan beberapa lembar mengabaikan fungsi IFERROR. Jadi, mari kita lihat langkah-langkah yang diberikan di bawah ini Langkah
Terakhir, Anda akan melihat tanda tertulis dan viva dari semua kandidat. Selain itu, Anda akan melihat kesalahan #N/A di mana nama tidak ada di lembar yang disebutkan Batasan Fungsi VLOOKUP dan Beberapa Alternatif di Excel
Cara Menerapkan Rumus VLOOKUP di Excel dengan Banyak Buku KerjaPada bagian ini, kita akan membahas cara menerapkan rumus VLOOKUP di Excel dengan beberapa buku kerja. Sekarang, mari kita buat workbook berikut bernama Mock Test Marks. Selain itu, di buku kerja itu, ada tiga lembar kerja juga. Yaitu Minggu 1, Minggu 2, dan Minggu 3 Saat ini, kami ingin membandingkan nilai tertulis awal dan akhir yang diperoleh para kandidat. Pada awalnya, kami menemukan tanda tertulis terakhir. Di sini, Anda dapat menemukannya dengan mengikuti salah satu metode sebelumnya. Sekarang, kami akan mengekstrak tanda tertulis awal dari buku kerja lain
Di sini, saat menggunakan rumus ini, Anda harus membuka kedua buku kerja tersebut. Jika tidak, Anda harus menggunakan jalur/lokasi isian alih-alih hanya menggunakan nama file
Terakhir, Anda akan melihat tanda tertulis akhir dan awal untuk semua kandidat Bagian PraktekSekarang, Anda bisa mempraktekkan sendiri cara yang telah dijelaskan KesimpulanDengan menggunakan metode ini, Anda dapat menggunakan fungsi VLOOKUP Excel sebagai rumus untuk mengekstrak data dari beberapa lembar dalam buku kerja. Jadi, apakah Anda memiliki pertanyaan lain? Bagaimana cara menarik nilai dari beberapa lembar di Excel?Pada tab Data, di bawah Alat, klik Konsolidasi. Dalam kotak Fungsi, klik fungsi yang ingin digunakan Excel untuk menggabungkan data. Di setiap lembar sumber, pilih data Anda, lalu klik Tambahkan
Bisakah VLOOKUP menarik dari beberapa lembar?Excel juga memungkinkan Anda menggunakan VLOOKUP di beberapa lembar jika Anda ingin menerapkan rumus ke beberapa lembar secara bersamaan. Kami melakukan tindakan ini dalam kombinasi dengan rumus IFERROR.
Bagaimana Anda mengisi data secara otomatis dari beberapa lembar ke master Excel?Cara mengumpulkan data dari beberapa sheet ke master sheet di. . Di lembar baru buku kerja yang ingin Anda kumpulkan datanya dari lembar, klik Data > Gabungkan Dalam dialog Konsolidasi, lakukan seperti ini. (1 Pilih satu operasi yang ingin Anda lakukan setelah menggabungkan data dalam daftar tarik-turun Fungsi;. Klik Oke Bagaimana cara melakukan pencarian di beberapa lembar?Fungsi VLOOKUP hanya dapat melakukan pencarian pada satu kumpulan data. Jika kita ingin melakukan pencarian di antara beberapa set data yang disimpan di lembar berbeda, kita dapat menggabungkan semua set data menjadi satu set atau menggunakan Fungsi IFERROR bersama dengan VLOOKUP untuk melakukan beberapa pencarian dalam satu formula |