Mencari nilai dengan VLOOKUP, INDEX, atau MATCH

Anda mempunyai daftar nomor kantor, dan Anda ingin mengetahui karyawan yang berada di setiap kantor. Namun lembar bentang sangat besar, jadi apa yang bisa Anda lakukan? Menggunakan fungsi pencarian. Fungsi VLOOKUP dan HLOOKUP adalah dua fungsi yang sangat berguna, begitu pula INDEX dan MATCH.

Catatan:  Jika Anda mencoba menemukan Panduan Lookup, fitur itu tidak lagi menjadi komponen dari Excel.

Ini adalah pengingat cepat tentang cara menggunakan VLOOKUP.

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

Argumen pertama—bagian data yang diperlukan untuk berfungsi—adalah nilai yang ingin Anda temukan. Itu bisa berupa referensi sel atau nilai yang sulit seperti "smith" atau 21.000. Argumen kedua adalah rentang sel yang menurut perkiraan Anda berisi nilai yang ingin Anda temukan. Dalam contoh ini, adalah C2-C7. Argumen ketiga adalah kolom di rentang sel itu yang berisi nilai yang ingin Anda lihat.

Argumen keempat adalah opsional. Anda bisa memasukkan 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 memberikan Anda fleksibilitas lebih besar.

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

Penggunaan umum fungsi VLOOKUP

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

Begitu Anda terbiasa dengan VLOOKUP, fungsi HLOOKUP seharusnya tidak sulit untuk digunakan. Anda memasukkan argumen yang sama, namun menemukan nilai di baris dan bukan di kolom.

Cobalah

Jika Anda ingin bermain-main dengan fungsi lookup sebelum Anda mencobanya dengan data Anda sendiri, berikut beberapa data sampel. Beberapa orang suka menggunakan VLOOKUP dan HLOOKUP, ada juga yang memilih menggunakan INDEX dan MATCH bersama-sama. Cobalah setiap metode dan lihat mana yang paling Anda suka.

VLOOKUP bekerja

Salin semua sel di tabel ini dan tempelkanlah ke sel A1 di lembar kerja kosong di Excel.

Petunjuk    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.

=VLOOKUP(1,A2:C10,2)

'=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.

=VLOOKUP(1,A2:C10,3,TRUE)

'=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.

=VLOOKUP(0,7,A2:C10,3,FALSE)

'=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.

=VLOOKUP(0,1,A2:C10,2,TRUE)

=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.

=VLOOKUP(2,A2:C10,2,TRUE)

HLOOKUP bekerja

Salin semua sel di tabel ini dan tempelkanlah ke sel A1 di lembar kerja kosong di Excel.

Petunjuk    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).

=HLOOKUP("Poros",A1:C4,2,TRUE)

'=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).

=HLOOKUP("Bantalan",A1:C4,3,FALSE)

'=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.

=HLOOKUP("B",A1:C4,3,TRUE)

'=HLOOKUP("Baut", A1:C4, 4)

Mencari "Baut" di baris 1, dan mengembalikan nilai dari baris 4 yang berada di kolom yang sama (Kolom C).

=HLOOKUP("Baut",A1:C4,4)

'=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.

=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)

INDEX dan MATCH bekerja

Contoh ini menggunakan fungsi INDEX dan MATCH bersama-sama untuk mengembalikan nomor faktur paling awal dan tanggal terkaitnya 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.

Petunjuk    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

Selengkapnya tentang fungsi pencarian

Atas Halaman

Kembangkan keterampilan 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.

×