Mencari nilai dengan VLOOKUP, INDEX, atau MATCH

Catatan: Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Anggap bahwa Anda memiliki daftar nomor lokasi kantor, dan Anda perlu mengetahui karyawan yang berada di setiap office. Lembar bentang sangat besar, sehingga Anda mungkin berpikir adalah tantangan tugas. Benar-benar cukup mudah dilakukan dengan fungsi lookup.

Fungsi VLOOKUP dan HLOOKUP , bersama dengan INDEX dan MATCH,adalah beberapa fungsi yang paling berguna dalam Excel.

Catatan: Panduan pencarian fitur itu tidak lagi tersedia di Excel.

Berikut adalah contoh cara menggunakan VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

Dalam contoh ini, B2 adalah argumenpertama — elemen data yang fungsi perlu bekerja. Untuk VLOOKUP, ini argumen pertama adalah nilai yang ingin Anda temukan. Argumen bisa berupa referensi sel, atau nilai tetap seperti "smith" atau 21.000. Argumen kedua adalah rentang sel, C2-:E7, di mana untuk mencari nilai yang ingin Anda temukan. Argumen ketiga adalah kolom dalam rentang sel yang berisi nilai yang Anda cari.

Argumen keempat bersifat opsional. Masukkan TRUE atau FALSE. Jika Anda masukkan TRUE, atau biarkan argumen yang kosong, fungsi akan mengembalikan kecocokan yang mendekati nilai yang Anda tentukan di argumen pertama. Jika Anda memasukkan FALSE, maka fungsi akan cocok dengan nilai dengan argumen pertama. Dengan kata lain, biarkan kosong argumen keempat — atau memasukkan TRUE — memberi Anda lebih fleksibel.

Contoh ini memperlihatkan kepada Anda cara kerja fungsi. Ketika Anda memasukkan nilai dalam sel B2 (argumen pertama), VLOOKUP mencari sel dalam rentang C2:E7 (ke-2 argumen) dan mengembalikan kecocokan yang mendekati terdekat dari kolom ketiga di rentang tersebut, kolom E (ke-3 argumen).

Penggunaan umum fungsi VLOOKUP

Argumen keempat kosong, jadi fungsi mengembalikan kecocokan yang mendekati. Jika tidak, Anda harus memasukkan salah satu nilai di kolom C atau D untuk mendapatkan hasil yang sama sekali.

Bila Anda sudah terbiasa dengan VLOOKUP, fungsi HLOOKUP secara merata mudah digunakan. Anda memasukkan argumen yang sama, tetapi mencari di baris dan bukan kolom.

Menggunakan INDEX dan MATCH dan bukan VLOOKUP

Ada beberapa keterbatasan dengan menggunakan VLOOKUP — fungsi VLOOKUP bisa hanya mencari nilai dari kiri ke kanan. Ini berarti bahwa kolom yang berisi nilai yang Anda Cari harus selalu berada di sebelah kiri kolom yang berisi nilai yang dikembalikan. Sekarang jika lembar bentang Anda tidak dibuat dengan cara ini, lalu menggunakan VLOOKUP. Gunakan kombinasi fungsi INDEX dan MATCH.

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.

Untuk contoh menggunakan INDEX dan MATCH dan bukan VLOOKUP, lihat artikel https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ dengan Bill Jelen, Microsoft MVP.

Cobalah

Jika Anda ingin bereksperimen dengan fungsi pencarian sebelum Anda mencobanya dengan data Anda sendiri, berikut adalah beberapa contoh data.

Contoh VLOOKUP di kantor

Salin data berikut ini ke 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 (tabBeranda , 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 (tabBeranda , 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

INDEX dan MATCH contoh

Contoh terakhir ini menggunakan fungsi INDEX dan MATCH bersama-sama untuk mengembalikan nomor faktur paling awal dan tanggal terkait untuk setiap lima kota. Karena tanggal dikembalikan sebagai angka, kami menggunakan fungsi TEXT untuk memformat sebagai tanggal. Fungsi INDEX benar-benar menggunakan hasil dari fungsi MATCH sebagai argumen. Kombinasi fungsi INDEX dan MATCH digunakan dua kali pada setiap rumus – pertama, untuk mengembalikan jumlah faktur, lalu kembali 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 (tabBeranda , grup perataan ).

Faktur

Kota

Tanggal Faktur

Faktur paling awal menurut kota, dengan tanggal

3115

Ambon

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

Ambon

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

Ambon

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

Ambon

27/04/2012

3350

Ambon

28/04/2012

3390

Ambon

01/05/2012

3441

Ambon

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)

Menggunakan argumen table_array dalam fungsi VLOOKUP

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×