Lompat ke konten Lompat ke sidebar Lompat ke footer

Rumus VLOOKUP Beda Sheet dan 2 Kriteria di Excel (Nama Kembar Beda Kelas)

Rumus VLOOKUP beda sheet dan 2 kriteria (nama + kelas) di Excel untuk nama siswa kembar

Untuk mengambil data dari sheet lain dengan VLOOKUP, tulis nama sheet di depan rentangnya: =VLOOKUP(A5,'Data Siswa'!$B$5:$G$104,2,FALSE). Kalau pencariannya butuh 2 kriteria, misalnya nama dan kelas karena ada nama kembar, buat satu kolom bantu yang menggabungkan keduanya (=C5&"|"&D5), lalu cari gabungan itu: =VLOOKUP(A19&"|"&B19,'Data Siswa'!$A$5:$G$104,5,FALSE). Dua cara ini dipakai di template di bawah untuk mencari NISN dan nilai siswa.

Dicek terakhir: 16 November 2026 oleh Arfan Putra. Rumus template dihitung ulang dengan mesin rumus dan dicocokkan dengan pencarian manual untuk 53 siswa contoh, termasuk 3 nama kembar di kelas berbeda, NISN yang tidak ada, NISN tanpa angka 0 di depan, dan nama dengan spasi berlebih. Ada 1.232 hasil yang dicek, dan semuanya cocok. Aturan fungsi dicocokkan dengan halaman Microsoft Support di bagian Sumber.

Unduh template gratis: VLOOKUP beda sheet + 2 kriteria (Excel). Isinya sheet Data Siswa (100 baris, sudah ada kolom bantu) dan sheet Cari (cari lewat NISN, dan cari lewat nama + kelas). Nama dan NISN fiktif.

Cara Kerja VLOOKUP Singkatnya

Bentuk rumusnya: =VLOOKUP(nilai_dicari, rentang_tabel, nomor_kolom, FALSE).

  • nilai_dicari harus ada di kolom pertama rentang tabel. Kalau rentangnya B5:G104, nilai yang dicari harus ada di kolom B.
  • nomor_kolom dihitung dari kolom pertama rentang, bukan dari kolom A lembar kerja. Di rentang B5:G104, kolom B = 1, C = 2, D = 3, dan seterusnya.
  • FALSE berarti cocok persis. Untuk data siswa selalu pakai FALSE. Kalau argumen ini dikosongkan, Excel memakai pencocokan perkiraan (TRUE), yang mengharuskan kolom pertama diurutkan. Hasilnya bisa salah tanpa pesan error.

Langkah 1: Siapkan Data di Sheet Sendiri

Di template, sheet Data Siswa berisi kolom berikut mulai baris 5:

A Kunci (bantu)B NISNC NamaD KelasE MatematikaF IPAG B. Indonesia
Muh. Rizki|VII-A3138383999Muh. RizkiVII-A819864
Muh. Rizki|VII-C3143288585Muh. RizkiVII-C615777

Kolom NISN diformat sebagai Teks, supaya angka 0 di depan tidak hilang. Kolom A diisi rumus, jadi tidak perlu diketik. Isinya dijelaskan di Langkah 3.

Langkah 2: VLOOKUP Beda Sheet (Cari Berdasarkan NISN)

Di sheet Cari, ketik NISN di A5. Nama siswa muncul di B5 dengan rumus:

=IF(A5="","",IFERROR(VLOOKUP(A5,'Data Siswa'!$B$5:$G$104,2,FALSE),"NISN tidak ditemukan"))
  • 'Data Siswa'! adalah nama sheet sumber, diikuti tanda seru. Nama sheet yang mengandung spasi harus diapit tanda kutip tunggal. Cara termudah: saat mengetik rumus, klik tab sheet Data Siswa lalu blok rentangnya, maka Excel menuliskan nama sheet dan tanda kutipnya sendiri.
  • Rentangnya dimulai dari kolom B (NISN), bukan A, karena NISN-lah yang dicari. Nama ada di kolom C, yaitu kolom ke-2 dari B, jadi nomor kolomnya 2.
  • IFERROR mengganti #N/A dengan tulisan "NISN tidak ditemukan". IF(A5="","", ...) membuat baris yang belum diisi tetap kosong.

Kelas, Matematika, IPA, dan Bahasa Indonesia diambil dengan rumus yang sama, hanya nomor kolomnya diganti menjadi 3, 4, 5, dan 6.

Langkah 3: Buat Kolom Bantu untuk 2 Kriteria

Masalahnya muncul saat mencari berdasarkan nama. Di data contoh ada dua siswa bernama Muh. Rizki, satu di VII-A dan satu di VII-C. VLOOKUP selalu berhenti di baris pertama yang cocok, sehingga nilai Muh. Rizki VII-C ikut terambil dari Muh. Rizki VII-A.

VLOOKUP hanya bisa mencari satu nilai, jadi kedua kriteria digabung menjadi satu teks di kolom paling kiri. Isi A5 di sheet Data Siswa, lalu tarik ke bawah:

=IF(C5="","",C5&"|"&D5)

Hasilnya Muh. Rizki|VII-A dan Muh. Rizki|VII-C, sehingga keduanya sekarang berbeda. Tanda | hanya pemisah supaya gabungannya tidak tertukar. Tanpa pemisah, nama "Andi" + kelas "11A" dan nama "Andi1" + kelas "1A" sama-sama menjadi "Andi11A". Kolom bantu harus berada di kiri kolom yang ingin diambil, karena VLOOKUP hanya bisa mengambil data ke arah kanan.

Langkah 4: VLOOKUP dengan 2 Kriteria

Di bagian 2 sheet Cari, isi nama di A19 dan pilih kelas di B19. NISN-nya diambil dengan:

=IF(A19="","",IFERROR(VLOOKUP(A19&"|"&B19,'Data Siswa'!$A$5:$G$104,2,FALSE),"tidak ditemukan"))

Nilai yang dicari disusun dengan cara yang sama persis seperti kolom bantu: nama, tanda |, lalu kelas. Rentangnya sekarang dimulai dari kolom A (kolom bantu), jadi NISN di kolom B adalah kolom ke-2, Matematika di E adalah kolom ke-5, IPA ke-6, dan Bahasa Indonesia ke-7.

Kolom G di bagian 2 sengaja diisi VLOOKUP dengan nama saja sebagai pembanding. Untuk Muh. Rizki VII-C, kolom G menampilkan 81 (nilai Matematika milik Muh. Rizki VII-A), sedangkan kolom D (2 kriteria) menampilkan 61, nilai yang benar.

Cara Lain: INDEX dan MATCH

Kalau kolom yang ingin diambil ada di kiri kolom pencarian, VLOOKUP tidak bisa dipakai. Gabungan INDEX dan MATCH bisa mengambil ke arah mana saja. Contoh mengambil NISN (kolom B) dari nama (kolom C):

=INDEX('Data Siswa'!$B$5:$B$104,MATCH(A19,'Data Siswa'!$C$5:$C$104,0))

MATCH dengan angka 0 mencari yang cocok persis dan mengembalikan posisinya di dalam rentang, lalu INDEX mengambil isi kolom NISN di posisi yang sama. Cara ini juga dipakai di artikel rapor bayangan otomatis di Excel. Masalah nama kembar tetap sama, sehingga untuk 2 kriteria kolom bantu tetap jadi cara paling sederhana. Kalau Excel Anda sudah punya fungsi XLOOKUP, fungsi itu juga bisa mengambil data ke kiri. Template ini sengaja tidak memakainya supaya tetap jalan di Excel versi lama.

Catatan penulisan rumus: rumus di artikel ini memakai koma (,) sebagai pemisah. Kalau Excel menolak rumusnya, ganti koma dengan titik koma (;) sesuai pengaturan wilayah di Windows.

Kalau Hasilnya Error

  • #N/A padahal NISN-nya ada. Biasanya NISN di Data Siswa tersimpan sebagai angka, sedangkan yang diketik di sheet Cari tersimpan sebagai teks, atau sebaliknya. Akibatnya angka 0 di depan hilang di salah satu sisi. Samakan formatnya: format kedua kolom sebagai Teks, lalu ketik ulang NISN-nya. Di template, NISN tanpa angka 0 di depan sengaja diuji dan hasilnya memang "NISN tidak ditemukan".
  • #N/A karena spasi. Nama "Nur Aini " (ada spasi di belakang) tidak sama dengan "Nur Aini". Ini sering terjadi pada data hasil salinan dari aplikasi lain. Bersihkan dengan =TRIM(C5) di kolom baru, lalu tempel sebagai nilai (Paste Values).
  • #REF! Nomor kolom lebih besar dari jumlah kolom di rentang. Contohnya rentang B5:G104 hanya punya 6 kolom, jadi nomor kolom 7 menghasilkan #REF!.
  • Hasilnya data siswa lain, bukan error. Ada dua penyebab. Argumen terakhir dikosongkan atau diisi TRUE (pencocokan perkiraan), atau nama yang dicari ada lebih dari satu. Pakai FALSE, dan untuk nama kembar pakai kolom bantu Nama|Kelas.
  • Hasilnya bergeser saat rumus ditarik ke bawah. Rentang tabel tidak dikunci. Pakai tanda $: 'Data Siswa'!$B$5:$G$104.
  • Data baru di bawah baris 104 tidak ditemukan. Rentang rumus berhenti di baris 104. Perpanjang di semua rumus sekaligus dengan Find & Replace (Ctrl+H): ganti $104 menjadi nomor baris terakhir yang baru, lalu salin rumus kolom bantu A ke baris baru.

Sumber