Kecocokan Persis vs Perkiraan

Argumen terakhir VLOOKUP, yaitu pencarian_rentang, menentukan cara kerja pencarian. Argumen ini kecil, tetapi sangat penting:

• FALSE (atau 0) berarti pencocokan tepat
• TRUE (atau 1) berarti pencocokan perkiraan

Memilih argumen yang salah adalah salah satu kesalahan lembar bentang yang paling umum. Pelajaran ini menjelaskan kapan tepatnya Anda harus menggunakan masing-masing pilihan.

Pencocokan tepat mencari nilai yang benar-benar sama. Jika nilai tersebut tidak ada, VLOOKUP mengembalikan galat #N/A, bukan menebak.

Gunakan FALSE saat Anda mencari pengidentifikasi unik seperti kode produk, pengidentifikasi karyawan, atau alamat surel, yang hanya dapat dianggap benar jika benar-benar cocok.

Tabel tidak perlu diurutkan untuk pencocokan tepat. VLOOKUP memindai tabel sampai menemukan nilainya.

Dengan tabel harga kita (A100 Apel 0.50, B200 Pisang 0.30, C300 Ceri 1.20), pencarian tepat untuk kode yang ada akan bekerja dengan sempurna:

Mengembalikan 1.20. Namun, jika Anda mencari kode yang tidak ada, seperti "Z999", Anda mendapatkan #N/A. Galat itu sebenarnya berguna: galat tersebut memberi tahu Anda bahwa item itu benar-benar tidak ada, bukan mengembalikan nilai terdekat yang salah.

Pencocokan perkiraan mencari nilai terbesar yang kurang dari atau sama dengan nilai_pencarian Anda. Pencocokan ini dibuat untuk rentang dan tingkatan, bukan pengidentifikasi yang harus tepat.

Penggunaan klasiknya adalah tabel tingkatan: lapisan pajak, rentang biaya kirim, batas nilai, atau potongan berdasarkan jumlah, ketika suatu nilai berada di antara dua ambang batas.

TRUE memiliki satu aturan penting yang akan dibahas pada bagian berikutnya.

Agar pencocokan perkiraan berfungsi, kolom pertama harus diurutkan dalam urutan menaik (dari yang terkecil ke yang terbesar). VLOOKUP menelusuri kolom dan berhenti pada nilai terakhir yang tidak melebihi nilai_pencarian Anda.

Jika kolom tidak diurutkan, TRUE akan mengembalikan jawaban yang tidak dapat diprediksi dan salah tanpa menampilkan galat apa pun. Kegagalan tanpa pesan inilah yang membuat banyak orang menghindari TRUE, kecuali jika mereka memang membutuhkan pengelompokan berdasarkan rentang.

Misalkan tabel penilaian berada di A1:B5 dan diurutkan menaik berdasarkan skor minimum:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

Skor 76 seharusnya mengembalikan C karena 76 berada dalam rentang 70–79. Rumusnya menggunakan TRUE agar menemukan ambang batas tertinggi yang tidak melebihi 76:

Dengan nilai_pencarian 76 dan TRUE, VLOOKUP membaca ambang batas yang telah diurutkan: 0, 60, 70, 90... Fungsi ini membandingkan setiap nilai.

• 0 kurang dari atau sama dengan 76: lanjutkan
• 60 kurang dari atau sama dengan 76: lanjutkan
• 70 kurang dari atau sama dengan 76: lanjutkan
• 90 lebih besar dari 76: berhenti

Fungsi ini kembali ke baris valid terakhir (70) dan mengembalikan rentangnya: C. Pencocokan perkiraan pada dasarnya adalah pencarian di antara beberapa ambang batas.

Jika Anda menghilangkan argumen keempat sepenuhnya, VLOOKUP secara bawaan menggunakan TRUE (perkiraan). Hal ini mengejutkan banyak orang yang mengira perilakunya akan tepat.

Rumus seperti =VLOOKUP(A2, Data!A:B, 2) pada daftar yang tidak diurutkan dapat mengembalikan nilai yang salah secara diam-diam. Kebiasaan yang aman adalah: selalu ketikkan FALSE, kecuali jika Anda memang sengaja melakukan pencocokan rentang pada tabel yang telah diurutkan.

Berikut perbedaannya secara sekilas:

• FALSE / tepat: tabel tidak harus diurutkan, nilai yang tidak ditemukan mengembalikan #N/A, paling cocok untuk pengidentifikasi dan kode
• TRUE / perkiraan: tabel harus diurutkan menaik, tidak pernah mengembalikan #N/A untuk nilai dalam rentang, paling cocok untuk tingkatan dan rentang

Pilih berdasarkan pertanyaan yang ingin Anda jawab: “apakah item tepat ini ada?” menggunakan FALSE; “termasuk rentang yang mana?” menggunakan TRUE.

Berat di D2 memerlukan biaya kirim dari tabel rentang yang telah diurutkan di A2:B6 (ambang batas 0, 1, 5, 10, 20 kg). Pencocokan perkiraan memilih tingkatan yang tepat:

Jika D2 bernilai 7, nilai tersebut masuk ke rentang 5 kg dan mengembalikan biaya rentang tersebut. Ubah beratnya, dan tingkatannya akan segera diperbarui tanpa perlu membuat daftar untuk setiap kemungkinan berat.

Ada juga aspek kinerja yang tidak terlalu terlihat. Pada tabel terurut yang sangat besar, pencocokan perkiraan (TRUE) dapat lebih cepat karena lembar bentang dapat melompat dengan cepat melalui nilai yang telah diurutkan, alih-alih memindai setiap baris.

Namun, kecepatan tidak pernah lebih penting daripada kebenaran. Jika data Anda tidak diurutkan atau Anda memerlukan pengidentifikasi yang tepat, selalu pilih FALSE. Jawaban salah yang cepat lebih buruk daripada jawaban benar yang sedikit lebih lambat. Untuk lembar bentang modern dan ukuran tabel yang umum, perbedaannya jarang terlihat, jadi gunakan FALSE sebagai pilihan bawaan demi keamanan.

Kesimpulan penting:

• FALSE = pencocokan tepat, tabel yang tidak diurutkan tidak menjadi masalah, nilai yang tidak ditemukan mengembalikan #N/A
• TRUE = pencocokan perkiraan, kolom pertama harus diurutkan menaik, menemukan nilai terbesar yang tidak melebihi target Anda
• Menghilangkan argumen akan menggunakan TRUE secara bawaan, jadi selalu nyatakan argumen tersebut
• Gunakan pencocokan tepat untuk pengidentifikasi dan kode; gunakan pencocokan perkiraan untuk tingkatan dan rentang

Selanjutnya, Anda akan membalik arahnya dan mencari secara mendatar pada baris dengan HLOOKUP.