Tutorial Excel ini menjelaskan cara menggunakan fungsi rumus VLOOKUP excel dengan sintaks dan contoh. Semoga di artikel ini dapat membantu anda menyelesaikan masalah mengenai rumus vlookup excel anda.
Keterangan
Fungsi VLOOKUP melakukan pencarian vertikal dengan mencari nilai di kolom pertama tabel dan mengembalikan nilai di baris yang sama di posisi index_number .
Fungsi VLOOKUP adalah fungsi bawaan di Excel yang dikategorikan sebagai Fungsi Pencarian/Referensi . Ini dapat digunakan sebagai fungsi lembar kerja (WS) di Excel. Sebagai fungsi lembar kerja, fungsi rumus VLOOKUP excel dapat dimasukkan sebagai bagian dari rumus dalam sel lembar kerja.
Fungsi VLOOKUP sebenarnya cukup mudah digunakan setelah Anda memahami cara kerjanya! Jika Anda ingin mengikuti tutorial ini, unduh contoh spreadsheet.
Sintaksis
Sintaks untuk fungsi VLOOKUP di Microsoft Excel adalah:
VLOOKUP( value, table, index_number, [approximate_match] )
Parameter atau Argumen
nilaiNilai yang akan dicari di kolom pertama tabel .mejaDua atau lebih kolom data yang diurutkan dalam urutan menaik.nomor indeksNomor kolom dalam tabel dari mana nilai yang cocok harus dikembalikan. Kolom pertama adalah 1.perkiraan_pertandinganPilihan. Masukkan FALSE untuk menemukan kecocokan yang tepat. Masukkan TRUE untuk menemukan perkiraan kecocokan. Jika parameter ini dihilangkan, TRUE adalah defaultnya.
Kembali
Fungsi VLOOKUP mengembalikan semua tipe data seperti string, numerik, tanggal, dll.
Jika Anda menentukan FALSE untuk parameter perkiraan_match dan tidak ada kecocokan persis yang ditemukan, maka fungsi VLOOKUP akan mengembalikan #N/A.
Jika Anda menentukan TRUE untuk parameter perkiraan_cocok dan tidak ada kecocokan persis yang ditemukan, maka nilai yang lebih kecil berikutnya akan dikembalikan.
Jika index_number kurang dari 1, fungsi rumus VLOOKUP excel akan mengembalikan #VALUE!.
Jika index_number lebih besar dari jumlah kolom dalam tabel , fungsi VLOOKUP akan mengembalikan #REF!.
Catatan
- Lihat juga fungsi HLOOKUP untuk melakukan pencarian horizontal.
- Lihat juga fungsi XLOOKUP yang merupakan fungsi pencarian generasi berikutnya yang berfungsi baik untuk pencarian vertikal maupun horizontal.
Berlaku untuk
- Excel untuk Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 untuk Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Jenis Fungsi
- Fungsi lembar kerja (WS)
Contoh (sebagai Fungsi Lembar Kerja)
Mari kita jelajahi cara menggunakan VLOOKUP sebagai fungsi lembar kerja di Microsoft Excel.
Berdasarkan spreadsheet Excel di atas, contoh VLOOKUP berikut akan kembali:
=VLOOKUP(10251, A1:B6, 2, FALSE) Result: "Pears" 'Returns value in 2nd column =VLOOKUP(10251, A1:C6, 3, FALSE) Result: $18.60 'Returns value in 3rd column =VLOOKUP(10251, A1:D6, 4, FALSE) Result: 9 'Returns value in 4th column =VLOOKUP(10248, A1:B6, 2, FALSE) Result: #N/A 'Returns #N/A error (no exact match) =VLOOKUP(10248, A1:B6, 2, TRUE) Result: "Apples" 'Returns an approximate match
Sekarang, mari kita lihat contoh =VLOOKUP(10251, A1:B6, 2, FALSE)yang mengembalikan nilai “Pears” dan lihat lebih dekat alasannya.
Parameter Pertama
Parameter pertama dalam fungsi VLOOKUP adalah nilai yang akan dicari dalam tabel data.
Dalam contoh ini, parameter pertama adalah 10251. Ini adalah nilai yang akan dicari VLOOKUP di kolom pertama tabel data. Karena ini adalah nilai numerik, Anda cukup memasukkan nomornya. Tetapi jika nilai pencarian adalah teks, Anda harus memasukkannya ke dalam tanda kutip ganda, misalnya:
=VLOOKUP("10251", A1:B6, 2, FALSE)
Parameter Kedua
Parameter kedua dalam fungsi rumus VLOOKUP excel adalah tabel atau sumber data tempat pencarian vertikal harus dilakukan.
Dalam contoh ini, parameter kedua adalah A1:B6 yang memberi kita dua kolom data untuk digunakan dalam pencarian vertikal – A1:A6 dan B1:B6. Kolom pertama dalam rentang (A1:A6) digunakan untuk mencari nilai Pesanan 10251. Kolom kedua dalam rentang (B1:B6) berisi nilai yang akan dikembalikan yang merupakan nilai Produk.
Rekomendasi artikel untuk anda : Tutorial Excel Lengkap SUMIF, SUMIFS, COUNTIF, dan COUNTIFS.
Parameter Ketiga
Parameter ketiga adalah nomor posisi dalam tabel di mana data yang dikembalikan dapat ditemukan. Nilai 1 menunjukkan kolom pertama dalam tabel. Kolom kedua adalah 2, dan seterusnya.
Dalam contoh ini, parameter ketiga adalah 2. Ini berarti kolom kedua dalam tabel adalah tempat kita akan menemukan nilai yang akan dikembalikan. Karena rentang tabel diatur ke A1:B6, nilai yang dikembalikan akan berada di kolom kedua di suatu tempat di rentang B1:B6.
Parameter Keempat
Terakhir dan yang paling penting adalah parameter keempat atau terakhir dalam VLOOKUP. Parameter ini menentukan apakah Anda mencari kecocokan persis atau kecocokan perkiraan.
Dalam contoh ini, parameter keempat adalah FALSE. Parameter FALSE berarti bahwa VLOOKUP mencari kecocokan TEPAT untuk nilai 10251. Parameter TRUE berarti kecocokan “dekat” akan dikembalikan. Karena VLOOKUP dapat menemukan nilai 10251 dalam rentang A1:A6, VLOOKUP mengembalikan nilai yang sesuai dari B1:B6 yaitu Pir.
Pencocokan Persis vs. Kecocokan Perkiraan
Untuk menemukan kecocokan yang tepat, gunakan FALSE sebagai parameter terakhir. Untuk menemukan kecocokan perkiraan, gunakan TRUE sebagai parameter akhir.
Mari kita cari nilai yang tidak ada dalam data kita untuk menunjukkan pentingnya parameter ini!
Benar-benar cocok
Gunakan FALSE untuk menemukan kecocokan yang tepat:
=VLOOKUP(10248, A1:B6, 2, FALSE) Result: #N/A
Jika tidak ada kecocokan persis yang ditemukan, #N/A dikembalikan.
Perkiraan Pertandingan
Gunakan TRUE untuk menemukan perkiraan kecocokan:
=VLOOKUP(10248, A1:B6, 2, TRUE) Result: "Apples"
Jika tidak ada kecocokan yang ditemukan, ia mengembalikan nilai yang lebih kecil berikutnya yang dalam hal ini adalah “Apel”.
VLOOKUP dari Lembar Lain
Anda dapat menggunakan VLOOKUP untuk mencari nilai saat tabel berada di lembar lain. Mari kita ubah contoh kita di atas dan asumsikan bahwa tabel berada di Sheet berbeda yang disebut Sheet2 dalam rentang A1:B6.
Kami dapat menulis ulang contoh asli kami di mana kami mencari nilai 10251 sebagai berikut:
=VLOOKUP(10251, Sheet2!A1:B6, 2, FALSE)
Dengan mendahului rentang tabel dengan nama sheet dan tanda seru, kita dapat memperbarui VLOOKUP kita untuk mereferensikan tabel di sheet lain.
VLOOKUP dari Lembar Lain dengan Spasi di Nama Lembar
Mari kita berikan satu komplikasi lagi. Apa yang terjadi jika nama sheet Anda mengandung spasi? Jika ada spasi dalam nama sheet, Anda perlu mengubah formula lebih lanjut.
Mari kita asumsikan bahwa tabel berada pada sebuah Sheet yang disebut “Test Sheet” dalam rentang A1:B6, sekarang kita perlu membungkus nama Sheet dalam tanda kutip tunggal sebagai berikut:
=VLOOKUP(10251, 'Test Sheet'!A1:B6, 2, FALSE)
Dengan menempatkan nama sheet dalam tanda kutip tunggal, kita dapat menangani nama sheet dengan spasi dalam fungsi VLOOKUP.
VLOOKUP dari Buku Kerja Lain
Anda bisa menggunakan VLOOKUP untuk mencari nilai di buku kerja lain. Misalnya, jika Anda ingin agar bagian tabel dari rumus VLOOKUP berasal dari buku kerja eksternal, kita bisa mencoba rumus berikut:
=VLOOKUP(10251, 'C:\[data.xlsx]Sheet1'!$A$1:$B$6, 2, FALSE)
Ini akan mencari nilai 10251 dalam file C:\data.xlxs di Lembar 1 di mana data tabel ditemukan dalam kisaran $A$1:$B$6.
Mengapa menggunakan Referensi Absolut?
Sekarang penting bagi kita untuk menutupi satu lagi kesalahan yang sering dilakukan. Ketika orang menggunakan fungsi VLOOKUP, mereka biasanya menggunakan referensi relatif untuk rentang tabel seperti yang kami lakukan dalam beberapa contoh kami di atas. Ini akan mengembalikan jawaban yang benar, tetapi apa yang terjadi ketika Anda menyalin rumus ke sel lain? Rentang tabel akan disesuaikan oleh Excel dan berubah relatif terhadap tempat Anda menempelkan rumus baru. Mari kita jelaskan lebih lanjut…
Jadi jika Anda memiliki rumus berikut di sel G1:
=VLOOKUP(10251, A1:B6, 2, FALSE)
Dan kemudian Anda menyalin rumus ini dari sel G1 ke sel H2, itu akan mengubah rumus VLOOKUP menjadi ini:
=VLOOKUP(10251, B2:C7, 2, FALSE)
Karena tabel Anda ditemukan dalam rentang A1:B6 dan bukan B2:C7, rumus Anda akan mengembalikan hasil yang salah di sel H2. Untuk memastikan bahwa rentang Anda tidak berubah, coba rujuk rentang tabel Anda menggunakan referensi absolut sebagai berikut:
=VLOOKUP(10251, $A$1:$B$6, 2, FALSE)
Sekarang jika Anda menyalin rumus ini ke sel lain, rentang tabel Anda akan tetap $A$1:$B$6.
Cara Menangani Kesalahan #N/A
Selanjutnya, mari kita lihat cara menangani instance di mana fungsi VLOOKUP tidak menemukan kecocokan dan mengembalikan kesalahan #N/A. Dalam kebanyakan kasus, Anda tidak ingin melihat #N/A tetapi lebih suka menampilkan hasil yang lebih ramah pengguna.
Misalnya, jika Anda memiliki rumus berikut:
=VLOOKUP(10248, $A$1:$B$6, 2, FALSE)
Alih-alih menampilkan kesalahan #N/A jika Anda tidak menemukan kecocokan, Anda dapat mengembalikan nilai “Tidak Ditemukan”. Untuk melakukan ini, Anda dapat mengubah rumus VLOOKUP Anda sebagai berikut:
=IF(ISNA(VLOOKUP(10248, $A$1:$B$6, 2, FALSE)), "Not Found", VLOOKUP(10248, $A$1:$B$6, 2, FALSE))
ATAU
=IFERROR(VLOOKUP(10248, $A$1:$B$6, 2, FALSE), "Not Found")
ATAU
=IFNA(VLOOKUP(10248, $A$1:$B$6, 2, FALSE), "Not Found")
Rumus ini menggunakan fungsi ISNA , IFERROR dan IFNA untuk mengembalikan “Tidak Ditemukan” jika kecocokan tidak ditemukan oleh fungsi VLOOKUP.
Ini adalah cara yang bagus untuk merapikan spreadsheet Anda sehingga Anda tidak melihat kesalahan Excel tradisional.
2 comments