Excel mengembalikan nilai dari beberapa lembar

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

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 IFERROR

Fungsi 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

=IFERROR(VLOOKUP(B3,'Department A'!$B$3:$C$7,2,FALSE),VLOOKUP(B3,'Department B'!$B$3:$C$7,2,FALSE))

Mari telusuri rumusnya

Fungsi VLOOKUP

Inilah VLOOKUP pertama

=VLOOKUP(B3,'Dept. A'!$B$3:$C$7,2,FALSE)

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

=VLOOKUP(B3,'Dept. B'!$B$3:$C$7,2,FALSE)

Fungsi IFERROR

Kami 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

=IFERROR(E3,F3)

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

=IFERROR(VLOOKUP(B3,'Department A'!$B$3:$C$7,2,FALSE),VLOOKUP(B3,'Department B'!$B$3:$C$7,2,FALSE))

VLOOKUP – Lebih dari 2 Lembar Sekaligus

Untuk lebih dari dua lembar, kita perlu menambahkan satu VLOOKUP dan satu IFERROR per lembar tambahan ke rumus di atas. Berikut rumus untuk 3 lembar

=IFERROR(IFERROR(VLOOKUP(B3,'Dept. A'!$B$3:$C$7,2,FALSE),VLOOKUP(B3,'Dept. B'!$B$3:$C$7,2,FALSE)),VLOOKUP(B3,'Dept. C'!$B$3:$C$7,2,FALSE))

VLOOKUP Beberapa Lembar Sekaligus di Google Lembar

Meskipun 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

=VLOOKUP(B3,{'Department A'!$B$3:$C$7;'Department B'!$B$3:$C$7;'Department C'!$B$3:$C$7},2,FALSE)

Mari uraikan dan visualisasikan rumusnya

Tumpuk semua kumpulan data secara vertikal dengan memisahkan rentang sel menggunakan titik koma di dalam tanda kurung kurawal

={'Department A'!$B$3:$C$7;'Department B'!$B$3:$C$7;'Department C'!$B$3:$C$7}

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 Latihan

Anda 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])

• Fungsi ini mengambil rentang sel yang disebut table_array sebagai argumen
• Kemudian, cari nilai tertentu yang disebut lookup_value di kolom pertama table_array
• Selain itu, cari pencocokan perkiraan jika argumen [range_lookup] BENAR, jika tidak, cari pencocokan tepat. Di sini, standarnya adalah BENAR
• Jika menemukan kecocokan lookup_value di kolom pertama table_array, pindahkan beberapa langkah ke kanan ke kolom tertentu (col_index_number)

Kemudian, kembalikan nilai dari sel itu

Penguraian 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 Lembar

Di 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 Terpisah

Di 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)

• Untuk mencari Nilai Tertulis Kandidat Minggu 1, masukkan rumus ini di sel C5 lembar kerja baru

=VLOOKUP(B5,'Week 1'!$B$5:$D$10,2,FALSE)_

• Selanjutnya, tekan ENTER

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“

• Kemudian, seret ikon Pegangan Isian

Akibatnya, kami melihat tanda hanya kandidat yang muncul di Minggu 1 yang ditampilkan, sisanya menunjukkan kesalahan

• Demikian pula untuk mencari tanda viva, tuliskan rumus berikut di sel D5

=VLOOKUP(B5,'Week 1'!$B$5:$D$10,3,FALSE)_

• Lalu, tekan ENTER

• Lalu, seret ikon Pegangan Isian untuk menerapkan rumus di sel lainnya

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 Excel

Kali 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”)))

• Sekarang, masukkan rumus berikut di sel C5 lembar “VLOOKUP & IFERROR”.

=IFERROR(VLOOKUP(B5,'Week 1'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5, 'Week 2'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5,'Week 3'!$B$5:$D$10,2,FALSE),"Absent")))

• Lalu, tekan ENTER

Hasilnya, Anda akan melihat tanda tertulis dari Alex Hales

Kemudian, kita akan menemukan tanda viva dari Alex Hales

• Jadi, tuliskan rumus berikut di sel D5

=IFERROR(VLOOKUP(B5,'Week 1'!$B$5:$D$10,3,FALSE),IFERROR(VLOOKUP(B5, 'Week 2'!$B$5:$D$10,3,FALSE),IFERROR(VLOOKUP(B5,'Week 3'!$B$5:$D$10,3,FALSE),"Absent")))

• Setelah itu, tekan ENTER

• Kemudian, pilih sel C5 dan D5
• Akibatnya, seret ikon Pegangan Isi untuk Mengisi Otomatis data yang sesuai di sel lainnya C6. D24

Terakhir, Anda akan melihat tanda tertulis dan viva untuk semua kandidat

Baca Selengkapnya. Contoh VLOOKUP Antara Dua Lembar di Excel

Bacaan Serupa

• VLOOKUP Tidak Berfungsi (8 Alasan & Solusi)
• LOOKUP Excel vs VLOOKUP. Dengan 3 Contoh
• Apa itu Array Tabel di VLOOKUP?
• Cara Menggunakan Nested VLOOKUP di Excel (3 Kriteria)
• Gunakan VLOOKUP dengan Banyak Kriteria di Excel (6 Metode + Alternatif)

3. Menggunakan Formula Gabungan untuk Mencari di Beberapa Lembar di Excel

Sebenarnya 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

• Pertama-tama, buat larik horizontal dengan nama semua lembar kerja. Di sini, kami telah membuat satu di F5. sel H5

• Kemudian, masukkan rumus berikut di sel C5

=IFERROR(VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B5:B10"),B5)>0,0))&"'!$B$5:$D$10"),2,FALSE),"Absent")

• Selanjutnya, tekan ENTER

Penguraian Formula

• Pertama, COUNTIF(INDIRECT(“‘”&$F$5. $H$5&”’. B5. B10"),B5) mengembalikan berapa kali nilai dalam sel B5 hadir dalam rentang 'Minggu 1'. B5. B10, 'Minggu 2'. B5. B10 dan 'Minggu 3'. B5. B10 masing-masing. [Ini $F$5. $H$5 adalah nama lembar kerja. Jadi rumus INDIRECT menerima 'Sheet_Name'. B5. B10. ]
  • Keluaran. {0,0,1}
• Kedua, MATCH(TRUE,{0,0,1}>0,0) kembali di lembar kerja mana nilai dalam B5 ada
  • Keluaran. 3
• Ini mengembalikan 3 karena nilai dalam B5 (Alex Hales) ada di lembar kerja no 3 (Minggu 3)
• Ketiga, INDEX($F$5. $H$5,1,3) mengembalikan nama lembar kerja dengan nilai di sel B5
  • Keluaran. “Minggu 3”
• Keempat, TIDAK LANGSUNG(“‘”&”Minggu 3″&”‘. $B$4. $D$9”) mengembalikan rentang total sel lembar kerja yang berisi nilai dalam B5
  • Keluaran. {"Nathan Mills",72,59;"Ruth Williamson",53,55;"Alex Hales",67,70;"Matthew Shepherd",76,45;"Christina Paul",69,75;"Ricardo Moyes"
• Terakhir, VLOOKUP(B5,{“Nathan Mills”,72,59;”Ruth Williamson”,53,55;”Alex Hales”,67,70;”Matthew Shepherd”,76,45;”Christina Paul”,69,
  • Keluaran. 67
• Jadi, inilah nilai ujian tertulis yang kami cari
• Dan jika nama tersebut tidak ditemukan di lembar kerja mana pun, itu akan mengembalikan "Tidak Ada" karena kami menyarangkannya di dalam fungsi IFERROR

Di sini, Anda bisa menggunakan rumus serupa untuk mengetahui tanda Viva para kandidat

• Jadi, ubah col_index_number dari 2 menjadi 3 dan tulis rumusnya

=IFERROR(VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B5:B10"),B5)>0,0))&"'!$B$5:$D$10"),3,FALSE),"Absent")

• Kemudian, tekan ENTER untuk mendapatkan hasilnya

• Kemudian seret ikon Fill Handle

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 Dinamis

Sampai 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

• Jadi sekarang kita ubah rumus di bagian sebelumnya menjadi ini

=IFERROR(VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B5:B10"),$B5)>0,0))&"'!$B$5:$D$10"),COLUMNS($C$1:D1),FALSE),"Absent")

• Lalu, tekan ENTER

• Setelah itu, seret ikon Fill Handle ke sisi kanan untuk mendapatkan tanda Viva

• Lalu, seret ikon Fill Handle ke bawah

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 Excel

Di 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

• Pertama, Anda harus memilih sel C5 baru di mana Anda ingin menyimpan tanda tertulis
• Kedua, Anda harus menggunakan rumus yang diberikan di bawah ini di sel C5

=VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!$B$5:$D$10"),B5)>0),0))&"'!$B$5:$D$10"),2,FALSE)

• Ketiga, tekan ENTER

• Demikian pula, gunakan rumus berikut di sel D5 untuk mendapatkan tanda Viva

=VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!$B$5:$D$10"),B5)>0),0))&"'!$B$5:$D$10"),3,FALSE)

• Selanjutnya, tekan ENTER

• Kemudian seret ikon Fill Handle

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

• Di sini, Anda tidak dapat menggunakan fungsi VLOOKUP saat lookup_value tidak ada di kolom pertama tabel. Misalnya, pada contoh sebelumnya, Anda tidak dapat menggunakan fungsi VLOOKUP untuk mengetahui nama kandidat yang mendapat nilai 90 pada ujian tertulis
• Namun, Anda dapat menggunakan fungsi IF, IFS, INDEX MATCH, XLOOKUP, atau FILTER Excel untuk menyelesaikan masalah ini (Di sini, Anda dapat mengunjungi artikel ini)
• Selanjutnya, VLOOKUP hanya mengembalikan nilai pertama jika lebih dari satu nilai cocok dengan lookup_value. Dalam kasus ini, Anda dapat menggunakan fungsi FILTER untuk mendapatkan semua nilai (Di sini, Anda dapat mengunjungi artikel ini)

Cara Menerapkan Rumus VLOOKUP di Excel dengan Banyak Buku Kerja

Pada 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

• Jadi, tuliskan rumus berikut di sel D5

=IFERROR(VLOOKUP(B5,'[Mock Test Marks.xlsx]Week 1'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5, '[Mock Test Marks.xlsx]Week 2'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5,'[Mock Test Marks.xlsx]Week 3'!$B$5:$D$10,2,FALSE),"Absent")))

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

• Lalu, tekan ENTER

• Lalu, seret ikon Fill Handle ke bawah

Terakhir, Anda akan melihat tanda tertulis akhir dan awal untuk semua kandidat

Bagian Praktek

Sekarang, Anda bisa mempraktekkan sendiri cara yang telah dijelaskan

Kesimpulan

Dengan 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?

Bisakah VLOOKUP menarik dari beberapa lembar?

Bagaimana Anda mengisi data secara otomatis dari beberapa lembar ke master Excel?

Bagaimana cara melakukan pencarian di beberapa lembar?