Pernahkah Anda memiliki spreadsheet besar dengan data di Excel dan membutuhkan cara mudah untuk memfilter dan mengekstrak informasi spesifik darinya? Jika Anda mempelajari cara menggunakan VLOOKUP di Excel , Anda dapat melakukan pencarian ini hanya dengan satu fungsi Excel^{(Excel function)} yang kuat .

Fungsi VLOOKUP^{(VLOOKUP function)} di Excel membuat takut banyak orang karena memiliki banyak parameter dan ada banyak cara untuk menggunakannya. Dalam artikel ini Anda akan mempelajari semua cara Anda dapat menggunakan VLOOKUP di Excel dan mengapa fungsinya sangat kuat.

Parameter VLOOKUP Di Excel^{(VLOOKUP Parameters In Excel)}

Saat Anda mulai mengetik =VLOOKUP( ke sel mana pun di Excel , Anda akan melihat pop-up yang menampilkan semua parameter fungsi yang tersedia.

Mari kita periksa masing-masing parameter ini dan apa artinya.

• lookup_value : Nilai yang Anda cari dari spreadsheet
• table_array : Rentang sel dalam lembar yang ingin Anda cari
• col_index_num : Kolom tempat Anda ingin menarik hasil Anda
• [range_lookup] : Mode pencocokan^{(Match mode)} ( TRUE = approximate , FALSE = exact )

Keempat parameter ini memungkinkan Anda melakukan banyak pencarian yang berbeda dan berguna untuk data di dalam kumpulan data yang sangat besar.

Contoh Excel VLOOKUP Sederhana^{(A Simple VLOOKUP Excel Example)}

VLOOKUP bukan salah satu fungsi ^{(VLOOKUP isn)}Excel dasar yang mungkin telah Anda pelajari, jadi mari kita lihat contoh sederhana untuk memulai.

Untuk contoh berikut, kami akan menggunakan spreadsheet besar nilai SAT untuk sekolah di Amerika ^(United) Serikat^(States) . Spreadsheet ini berisi lebih dari 450 sekolah bersama dengan skor SAT individu untuk membaca, matematika, dan menulis. Jangan^(Feel) ragu untuk mengunduh untuk mengikuti. Ada koneksi eksternal yang menarik data, jadi Anda akan mendapatkan peringatan saat membuka file, tetapi aman.

Akan sangat memakan waktu untuk menelusuri kumpulan data yang begitu besar untuk menemukan sekolah yang Anda minati.

Sebagai gantinya, Anda bisa membuat formulir sederhana di sel kosong di sisi tabel. Untuk melakukan pencarian ini, cukup buat satu bidang untuk Sekolah^(School) , dan tiga bidang tambahan untuk nilai membaca, matematika, dan menulis.

Selanjutnya, Anda harus menggunakan fungsi VLOOKUP^{(VLOOKUP function)} di Excel untuk membuat ketiga bidang ini berfungsi. Di bidang Membaca^(Reading) , buat fungsi VLOOKUP^{(VLOOKUP function)} sebagai berikut:

1. Ketik =VLOOKUP(
2. Pilih bidang Sekolah^{(School field)} , yang dalam contoh ini adalah I2 . Ketik koma.
3. Pilih seluruh rentang sel yang berisi data yang ingin Anda cari. Ketik koma.

Saat Anda memilih rentang, Anda bisa mulai dari kolom yang Anda gunakan untuk mencari (dalam hal ini kolom nama sekolah^{(school name column)} ), lalu pilih semua kolom dan baris lain yang berisi data.

Catatan^(Note) : Fungsi VLOOKUP^{(VLOOKUP function)} di Excel hanya dapat mencari melalui sel di sebelah kanan kolom pencarian^{(search column)} . Dalam contoh ini, kolom nama sekolah^{(school name column)} harus berada di sebelah kiri data yang Anda cari.

1. Selanjutnya, untuk mengambil skor Bacaan^{(Reading score)} , Anda harus memilih kolom ke-3 dari kolom yang dipilih paling kiri. Jadi, ketik 3 lalu ketik koma lainnya.
2. Terakhir, ketik FALSE untuk pencocokan tepat, dan tutup fungsi dengan a ) .

Fungsi VLOOKUP^{(VLOOKUP function)} terakhir Anda akan terlihat seperti ini:

=VLOOKUP(I2,B2:G461,3,FALSE)

Saat Anda pertama kali menekan Enter dan menyelesaikan fungsinya, Anda akan melihat bidang Bacaan^{(Reading field)} akan berisi #N/A .

Ini karena bidang Sekolah^{(School field)} kosong dan tidak ada fungsi VLOOKUP yang^{(VLOOKUP function)} dapat ditemukan. Namun, jika Anda memasukkan nama sekolah menengah mana pun yang ingin Anda cari, Anda akan melihat hasil yang benar dari baris tersebut untuk skor Membaca^{(Reading score)} .

Cara Mengatasi VLOOKUP Menjadi Peka Huruf Besar-kecil^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Anda mungkin memperhatikan bahwa jika Anda tidak mengetikkan nama sekolah dalam kasus yang sama seperti yang tercantum dalam kumpulan data, Anda tidak akan melihat hasil apa pun.

Ini karena fungsi VLOOKUP^{(VLOOKUP function)} peka huruf besar/kecil. Ini bisa mengganggu, terutama untuk kumpulan data yang sangat besar di mana kolom yang Anda telusuri tidak konsisten dengan huruf kapital.

Untuk menyiasatinya, Anda dapat memaksa apa yang Anda cari untuk beralih ke huruf kecil sebelum mencari hasilnya. Untuk melakukannya, buat kolom baru di sebelah kolom yang Anda cari. Ketik fungsi:

=TRIM(LOWER(B2))

Ini akan menurunkan huruf kecil nama sekolah^{(school name)} dan menghapus karakter asing (spasi) yang mungkin ada di sisi kiri atau kanan nama.

Tahan tombol Shift dan letakkan ^{(Shift key and place)}kursor mouse^{(mouse cursor)} di sudut kanan bawah sel pertama hingga berubah menjadi dua garis horizontal. Klik dua^(Double) kali mouse untuk mengisi otomatis seluruh kolom.

Terakhir, karena VLOOKUP akan mencoba menggunakan rumus daripada teks dalam sel ini, Anda perlu mengonversi semuanya menjadi nilai saja. Untuk melakukan ini, salin seluruh kolom, klik kanan di sel pertama, dan tempel nilai saja.

Sekarang semua data Anda dibersihkan di kolom baru ini, sedikit ubah fungsi VLOOKUP^{(VLOOKUP function)} Anda di Excel untuk menggunakan kolom baru ini daripada yang sebelumnya dengan memulai rentang pencarian^{(the lookup range)} di C2, bukan B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Sekarang Anda akan melihat bahwa jika Anda selalu mengetik penelusuran dengan huruf kecil, Anda akan selalu mendapatkan hasil penelusuran^{(search result)} yang baik .

Ini adalah tip Excel yang berguna^{(handy Excel tip)} untuk mengatasi fakta bahwa VLOOKUP peka terhadap huruf besar-kecil.

Perkiraan Pertandingan VLOOKUP

Meskipun contoh LOOKUP^{(LOOKUP example)} pencocokan tepat yang dijelaskan di bagian pertama artikel ini cukup mudah, perkiraan pencocokannya sedikit lebih rumit.

Perkiraan kecocokan paling baik digunakan untuk menelusuri rentang angka. Untuk melakukan ini dengan benar, rentang pencarian^{(search range)} perlu diurutkan dengan benar. Contoh terbaik dari ini adalah fungsi VLOOKUP^{(VLOOKUP function)} untuk mencari nilai huruf^{(letter grade)} yang sesuai dengan nilai angka^{(number grade)} .

Jika seorang guru memiliki daftar panjang nilai pekerjaan rumah siswa^{(student homework)} sepanjang tahun dengan kolom rata-rata^{(averaged column)} akhir , alangkah baiknya jika nilai huruf yang sesuai dengan nilai akhir itu muncul secara otomatis.

Ini dimungkinkan dengan fungsi VLOOKUP^{(VLOOKUP function)} . Yang diperlukan hanyalah tabel pencarian^{(lookup table)} di sebelah kanan yang berisi nilai huruf yang sesuai untuk setiap ^{(letter grade)}rentang skor^{(score range)} numerik .

Sekarang, dengan menggunakan fungsi VLOOKUP^{(VLOOKUP function)} dan perkiraan kecocokan, Anda dapat menemukan nilai huruf yang sesuai dengan rentang numerik yang benar.

Dalam fungsi VLOOKUP ini:

• lookup_value : F2, nilai rata-rata akhir
• table_array : I2:J8, Rentang pencarian nilai huruf^{(letter grade lookup range)}
• index_column : 2, kolom kedua di tabel pencarian^{(lookup table)}
• [range_lookup] : BENAR, perkiraan kecocokan

Setelah Anda menyelesaikan fungsi VLOOKUP^{(VLOOKUP function)} di G2 dan menekan Enter^{(G2 and press Enter)} , Anda dapat mengisi sisa sel menggunakan pendekatan yang sama yang dijelaskan di bagian terakhir. Anda akan melihat semua nilai huruf terisi dengan benar.

Perhatikan bahwa fungsi VLOOKUP^{(VLOOKUP function)} di Excel mencari dari ujung bawah kisaran nilai dengan ^{(grade range)}skor huruf^{(letter score)} yang ditetapkan ke atas kisaran skor huruf^{(letter score)} berikutnya .

Jadi, "C" harus menjadi huruf yang ditetapkan ke kisaran bawah (75), dan B ditetapkan ke bagian bawah (minimum) dari rentang hurufnya^{(letter range)} sendiri . VLOOKUP akan "menemukan" hasil untuk 60 (D) sebagai nilai perkiraan terdekat untuk apa pun antara 60 hingga 75.

VLOOKUP di Excel adalah fungsi yang sangat kuat yang telah tersedia sejak lama. Ini juga berguna untuk menemukan nilai yang cocok di mana saja dalam buku kerja Excel^{(Excel workbook)} .

Namun, perlu diingat bahwa pengguna Microsoft yang memiliki langganan bulanan Office 365 sekarang memiliki akses ke fungsi XLOOKUP yang lebih baru. Fungsi ini memiliki lebih banyak parameter dan fleksibilitas tambahan. Pengguna dengan langganan setengah tahunan harus menunggu pembaruan diluncurkan pada Juli 2020^{(July 2020)} .

How to Use VLOOKUP in Excel

Have yоu ever had a large spreadsheet with data in Excel and need an easy way to filter and extract specific іnformation from it? If you learn how to use VLOOKUP іn Excel, you can do thiѕ lookuр with just a singlе, powerfυl Exсel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• Tambahkan Regression Trendline Linear ke Excel Scatter Plot

• Cara Membuat Histogram di Excel

• Cara aman Password Protect A Excel File

• Cara Membuat Flowchart di Word and Excel

• Cara Create Labels di Word dari Excel Spreadsheet

• Cara Automatically Backup sebuah Word Document untuk onedrive

• Cara Membuat Distribution List di Outlook

• Cara Membuat Greeting Card dengan MS Word

• Cara Menemukan Nilai yang Cocok di Excel

• Tautkan Sel Antara Lembar dan Buku Kerja Di Excel

• Cara Membuka Banyak Instance Excel

• Cara Menggunakan Fungsi YEARFRAC di Excel

• Bagaimana Menambahkan dan Mencetak Gambar Latar Belakang Excel

• Cara Merekam Macro di Excel

• Gunakan Excel sebagai Alat untuk Menyalin Data dari Web

• Panduan Untuk Semua Ekstensi File Excel & Apa Artinya

• Cara Menggunakan Fitur Excel AutoRecover dan AutoBackup

• Cara Menemukan dan Menghitung Rentang di Excel

• Cara Menggunakan COUNTIFS, SUMIFS, AVERAGEIFS di Excel

• Cara Mengubah Background di Microsoft Teams