Tips
Coba gunakan fungsi XLOOKUP dan XMATCH baru, versi fungsi yang disempurnakan yang dijelaskan dalam artikel ini. Fungsi-fungsi baru ini bekerja dalam arah apa pun dan mengembalikan kecocokan persis secara default, membuatnya lebih mudah dan lebih nyaman digunakan daripada pendahulunya.
Misalnya Anda memiliki daftar nomor lokasi kantor, dan Anda perlu mengetahui karyawan mana yang ada di setiap kantor. Lembar bentang sangat besar, jadi Anda mungkin berpikir itu adalah tugas yang menantang. Sebenarnya sangat mudah dilakukan dengan fungsi pencarian.
Fungsi VLOOKUP dan HLOOKUP , bersama dengan INDEX dan MATCH, adalah beberapa fungsi yang paling berguna di Excel.
Catatan
Fitur Panduan Pencarian tidak lagi tersedia di Excel.
Berikut adalah contoh cara menggunakan VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
Dalam contoh ini, B2 adalah argumen pertama—elemen data yang diperlukan fungsi untuk bekerja. Untuk VLOOKUP, argumen pertama ini adalah nilai yang ingin Anda temukan. Argumen ini bisa berupa referensi sel, atau nilai tetap seperti "smith" atau 21.000. Argumen kedua adalah rentang sel, C2-:E7, untuk mencari nilai yang ingin Anda temukan. Argumen ketiga adalah kolom dalam rentang sel yang berisi nilai yang Anda cari.
Argumen keempat adalah opsional. Masukkan TRUE atau FALSE. Jika Anda memasukkan TRUE, atau membiarkan argumen kosong, fungsi mengembalikan kecocokan yang mendekati nilai yang Anda tentukan di argumen pertama. Jika Anda memasukan FALSE, fungsi akan mencocokkan nilai yang disediakan oleh argumen pertama. Dengan kata lain, membiarkan argumen keempat kosong—atau memasukkan TRUE—memberi Anda lebih banyak fleksibilitas.
Contoh ini memperlihatkan kepada Anda cara kerja fungsi. Saat Anda memasukkan nilai di sel B2 (argumen pertama), VLOOKUP mencari sel dalam rentang C2:E7 (argumen ke-2) dan mengembalikan kecocokan terdekat dari kolom ketiga dalam rentang, kolom E (argumen ke-3).
Argumen keempat kosong, sehingga fungsi mengembalikan kecocokan yang mendekati. Jika tidak, Anda harus memasukkan satu nilai dalam kolom C atau D untuk mendapatkan hasil.
Ketika Anda terbiasa dengan VLOOKUP, fungsi HLOOKUP sama mudah digunakan. Anda memasukkan argumen yang sama, tetapi mencari dalam baris dan bukan kolom.
Menggunakan INDEX dan MATCH, bukan VLOOKUP
Ada batasan tertentu dengan menggunakan VLOOKUP—fungsi VLOOKUP hanya dapat mencari nilai dari kiri ke kanan. Ini berarti bahwa kolom yang berisi nilai yang Anda cari harus selalu terletak di sebelah kiri kolom yang berisi nilai yang dikembalikan. Sekarang jika lembar bentang Anda tidak dibuat dengan cara ini, maka jangan gunakan VLOOKUP. Gunakan kombinasi fungsi INDEX dan MATCH sebagai gantinya.
Contoh ini memperlihatkan daftar kecil di mana nilai yang ingin kita cari, Chicago, tidak berada di kolom paling kiri. Jadi, kita tidak bisa menggunakan VLOOKUP. Sebagai gantinya, kita akan menggunakan fungsi MATCH untuk menemukan Chicago dalam rentang B1:B11. Chicago ditemukan di baris 4. Lalu, INDEX menggunakan nilai tersebut sebagai argumen, dan menemukan populasi untuk Chicago dalam kolomke 4 (kolom D). Rumus yang digunakan diperlihatkan di sel A14.
Cobalah
Jika Anda ingin bereksperimen dengan fungsi pencarian sebelum Anda mencobanya dengan data Anda sendiri, berikut ini beberapa contoh data.
Contoh VLOOKUP di kantor
Salin data berikut ini ke dalam lembar bentang kosong.
Tips
Sebelum Anda menempelkan data ke Excel, atur lebar kolom untuk kolom A hingga C menjadi 250 piksel, dan klik Bungkus Teks (tab Beranda, grup Perataan).
| Kerapatan | Kekentalan | Suhu |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Rumus | Deskripsi | Hasil |
| =VLOOKUP(1,A2:C10,2) | Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 1 di kolom A, menemukan nilai terbesar yang lebih kecil atau sama dengan 1 di kolom A yaitu 0,946, lalu mengembalikan nilai dari kolom B dalam baris yang sama. | 2,17 |
| =VLOOKUP(1,A2:C10,3,TRUE) | Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 1 di kolom A, menemukan nilai terbesar yang lebih kecil atau sama dengan 1 di kolom A yaitu 0,946, lalu mengembalikan nilai dari kolom C dalam baris yang sama. | 100 |
| =VLOOKUP(0,7,A2:C10,3,FALSE) | Dengan menggunakan hasil yang persis sama, rumus mencari nilai 0,7 dalam kolom A. Karena tidak ada hasil yang persis sama dalam kolom A, kesalahan dikembalikan. | #N/A |
| =VLOOKUP(0,1,A2:C10,2,TRUE) | Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 0,1 dalam kolom A. Karena 0,1 lebih kecil dari nilai terkecil dalam kolom A, nilai kesalahan dikembalikan. | #N/A |
| =VLOOKUP(2,A2:C10,2,TRUE) | Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 2 di kolom A, menemukan nilai terbesar yang kurang dari atau sama dengan 2 di kolom A, yaitu 1,29, lalu mengembalikan nilai dari kolom B dalam baris yang sama. | 1,71 |
Contoh HLOOKUP
Salin semua sel di tabel ini dan tempelkanlah ke sel A1 di lembar kerja kosong di Excel.
Tips
Sebelum Anda menempelkan data ke Excel, atur lebar kolom untuk kolom A hingga C menjadi 250 piksel, dan klik Bungkus Teks (tab Beranda, grup Perataan).
| Poros | Bantalan | Baut |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Rumus | Deskripsi | Hasil |
| =HLOOKUP("Poros", A1:C4, 2, TRUE) | Mencari "Poros" di baris 1, dan mengembalikan nilai dari baris 2 yang berada di kolom yang sama (kolom A). | 4 |
| =HLOOKUP("Bantalan", A1:C4, 3, FALSE) | Mencari "Bantalan" di baris 1, dan mengembalikan nilai dari baris 3 yang berada di kolom yang sama (kolom B). | 7 |
| =HLOOKUP("B", A1:C4, 3, TRUE) | Mencari "B" di baris 1, dan mengembalikan nilai dari baris 3 yang berada di kolom yang sama. Karena kecocokan yang persis untuk "B" tidak ditemukan, nilai terbesar dalam baris 1 yang kurang dari "B" digunakan: "Poros," di kolom A. | 5 |
| =HLOOKUP("Baut", A1:C4, 4) | Mencari "Baut" di baris 1, dan mengembalikan nilai dari baris 4 yang berada di kolom yang sama (Kolom C) | 11 |
| =HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) | Mencari angka 3 di konstanta array tiga baris, dan mengembalikan nilai dari baris 2 di kolom yang sama (dalam hal ini, ketiga). Ada tiga baris nilai dalam konstanta array, setiap baris dipisahkan oleh titik koma (;). Karena "c" ditemukan di baris 2 dan di kolom yang sama dengan 3, "c" dikembalikan. | c |
Contoh INDEX dan MATCH
Contoh terakhir ini menggunakan fungsi INDEX dan MATCH bersama-sama untuk mengembalikan nomor faktur paling awal dan tanggal yang sesuai untuk masing-masing dari lima kota. Karena tanggal dikembalikan sebagai angka, kita gunakan fungsi TEXT untuk memformatnya sebagai tanggal. Fungsi INDEX sebenarnya menggunakan hasil dari fungsi MATCH sebagai argumennya. Kombinasi fungsi INDEX dan MATCH digunakan dua kali dalam tiap rumus – pertama, untuk mengembalikan nomor faktur, lalu untuk mengembalikan tanggal.
Salin semua sel di tabel ini dan tempelkanlah ke sel A1 di lembar kerja kosong di Excel.
Tips
Sebelum Anda menempelkan data ke Excel, atur lebar kolom untuk kolom A hingga D menjadi 250 piksel, dan klik Bungkus Teks (tab Beranda, grup Perataan).
| Faktur | Kota | Tanggal Faktur | Faktur paling awal menurut kota, dengan tanggal |
|---|---|---|---|
| 3115 | Atlanta | 07/04/2012 | ="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Tanggal faktur: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"d/m/yy") |
| 3137 | Atlanta | 09/04/2012 | ="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Tanggal faktur: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"d/m/yy") |
| 3154 | Atlanta | 11/04/2012 | ="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Tanggal faktur: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"d/m/yy") |
| 3191 | Atlanta | 21/04/2012 | ="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Tanggal faktur: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"d/m/yy") |
| 3293 | Atlanta | 25/04/2012 | ="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Tanggal faktur: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"d/m/yy") |
| 3331 | Atlanta | 27/04/2012 | |
| 3350 | Atlanta | 28/04/2012 | |
| 3390 | Atlanta | 01/05/2012 | |
| 3441 | Atlanta | 02/05/2012 | |
| 3517 | Atlanta | 08/05/2012 | |
| 3124 | Austin | 09/04/2012 | |
| 3155 | Austin | 11/04/2012 | |
| 3177 | Austin | 19/04/2012 | |
| 3357 | Austin | 28/04/2012 | |
| 3492 | Austin | 06/05/2012 | |
| 3316 | Dallas | 25/04/2012 | |
| 3346 | Dallas | 28/04/2012 | |
| 3372 | Dallas | 01/05/2012 | |
| 3414 | Dallas | 01/05/2012 | |
| 3451 | Dallas | 02/05/2012 | |
| 3467 | Dallas | 02/05/2012 | |
| 3474 | Dallas | 04/05/2012 | |
| 3490 | Dallas | 05/05/2012 | |
| 3503 | Dallas | 08/05/2012 | |
| 3151 | New Orleans | 09/04/2012 | |
| 3438 | New Orleans | 02/05/2012 | |
| 3471 | New Orleans | 04/05/2012 | |
| 3160 | Tampa | 18/04/2012 | |
| 3328 | Tampa | 26/04/2012 | |
| 3368 | Tampa | 29/04/2012 | |
| 3420 | Tampa | 01/05/2012 | |
| 3501 | Tampa | 06/05/2012 |
Lihat Juga
Kartu Referensi Praktis: Penyegaran VLOOKUP
Fungsi pencarian dan referensi (referensi)