Note: The other languages of the website are Google-translated. Back to English
Masuk  \/ 
x
or
x
Daftar  \/ 
x

or

Fungsi VLOOKUP dengan beberapa contoh dasar dan lanjutan di Excel

Di Excel, fungsi VLOOKUP adalah fungsi yang hebat untuk sebagian besar pengguna Excel, yang digunakan untuk mencari nilai di paling kiri dari rentang data, dan mengembalikan nilai yang cocok di baris yang sama dari kolom yang Anda tentukan seperti gambar di bawah ini yang diperlihatkan . Tutorial ini membahas tentang cara menggunakan fungsi VLOOKUP dengan beberapa contoh dasar dan lanjutan di Excel.

Daftar Isi:

1. Pengenalan fungsi VLOOKUP - Sintaks dan Argumen

2. Contoh VLOOKUP dasar

3. Contoh VLOOKUP tingkat lanjut

4. VLOOKUP cocok nilai menjaga format sel

5. Unduh file contoh VLOOKUP


Pengenalan fungsi VLOOKUP - Sintaks dan Argumen

Sintaks fungsi VLOOKUP:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Argumen:

Nilai lookup: Nilai yang ingin Anda cari. Ini harus berada di kolom pertama dari rentang table_array.

Tabel_array: Rentang data atau tabel tempat kolom nilai pencarian dan kolom nilai hasil ditempatkan.

col_index_number: Jumlah kolom yang akan mengembalikan nilai yang cocok. Ini dimulai dengan 1 dari kolom paling kiri dalam larik tabel.

Range_lookup: Nilai logika yang menentukan apakah fungsi VLOOKUP ini akan mengembalikan kecocokan tepat atau kecocokan perkiraan.

  • Perkiraan kecocokan - 1 / BENAR: Jika pencocokan tepat tidak ditemukan, rumus mencari yang paling cocok - nilai terbesar yang lebih kecil dari nilai pencarian. Dalam kasus ini, Anda harus mengurutkan kolom pencarian dalam urutan menaik.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Benar-benar cocok - 0 / SALAH: Ini digunakan untuk mencari nilai yang sama persis dengan nilai pencarian. Jika pencocokan persis tidak ditemukan, nilai kesalahan # N / A akan dikembalikan.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Catatan:

  • 1. Fungsi Vlookup hanya mencari nilai dari kiri ke kanan.
  • 2. Jika ada beberapa nilai yang cocok berdasarkan nilai pencarian, hanya nilai pertama yang cocok yang akan dikembalikan dengan menggunakan fungsi Vlookup.
  • 3. Ini akan mengembalikan nilai kesalahan # N / A jika nilai pencarian tidak dapat ditemukan.

Contoh VLOOKUP dasar

1. Lakukan pencocokan persis dengan Vlookup dan perkiraan pencocokan Vlookup

Lakukan pencocokan persis Vlookup di Excel

Biasanya, jika Anda mencari kecocokan persis dengan fungsi Vlookup, Anda hanya perlu menggunakan FALSE di argumen terakhir.

Misalnya, untuk mendapatkan nilai Matematika yang sesuai berdasarkan nomor ID tertentu, lakukan seperti ini:

1. Terapkan rumus di bawah ini ke dalam sel kosong di mana Anda ingin mendapatkan hasilnya:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Dan kemudian, seret gagang isian ke sel yang Anda inginkan untuk mengisi rumus ini, dan Anda akan mendapatkan hasil yang Anda butuhkan. Lihat tangkapan layar:

Catatan:

  • 1. Dalam rumus di atas, F2 adalah nilai yang ingin Anda kembalikan nilai pencocokannya, A2: D7 adalah larik tabel, jumlahnya 3 adalah nomor kolom dari mana nilai cocok Anda dikembalikan dan SALAH mengacu pada pencocokan persis.
  • 2. Jika nilai kriteria Anda tidak ditemukan dalam rentang data, nilai kesalahan # N / A akan ditampilkan.

Lakukan perkiraan pertandingan Vlookup di Excel

Perkiraan kecocokan berguna untuk mencari nilai di antara rentang data. Jika pencocokan persis tidak ditemukan, perkiraan Vlookup akan mengembalikan nilai terbesar yang lebih kecil dari nilai pencarian.

Misalnya, jika Anda memiliki data rentang berikut, pesanan yang ditentukan tidak ada di kolom Pesanan, bagaimana cara mendapatkan Diskon terdekat di kolom B?

1. Masukkan rumus berikut ke dalam sel tempat Anda ingin meletakkan hasilnya:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Kemudian, seret gagang isian ke sel untuk menerapkan rumus ini, dan Anda akan mendapatkan perkiraan kecocokan berdasarkan nilai yang diberikan, lihat tangkapan layar:

Catatan:

  • 1. Dalam rumus di atas, D2 adalah nilai yang ingin Anda kembalikan informasi relatifnya, A2: B9 adalah rentang data, jumlahnya 2 menunjukkan nomor kolom yang dikembalikan nilai cocok Anda dan BENAR mengacu pada perkiraan kecocokan.
  • 2. Perkiraan kecocokan akan mengembalikan nilai terbesar yang lebih kecil dari nilai pencarian spesifik Anda.
  • 3. Untuk menggunakan fungsi Vlookup untuk mendapatkan perkiraan nilai kecocokan, Anda harus mengurutkan kolom paling kiri dari rentang data dalam urutan menaik, jika tidak maka akan mengembalikan hasil yang salah.

2. Lakukan Vlookup peka huruf besar kecil di Excel

Secara default, fungsi Vlookup melakukan pencarian case insensitive yang berarti memperlakukan karakter huruf kecil dan huruf besar sebagai identik. Terkadang, Anda mungkin perlu melakukan pencarian case-sensitive di Excel, fungsi Index, Match dan Exact atau fungsi Lookup dan Exact dapat membantu Anda.

Misalnya, saya memiliki rentang data berikut yang kolom ID berisi string teks dengan huruf besar atau kecil, sekarang, saya ingin mengembalikan skor Matematika yang sesuai dari nomor ID yang diberikan.

Rumus 1: Menggunakan fungsi EXACT, INDEX, MATCH

1. Silakan masukkan atau salin rumus array di bawah ini ke dalam sel kosong di mana Anda ingin mendapatkan hasilnya:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Lalu tekan Ctrl + Shift + Enter kunci secara bersamaan untuk mendapatkan hasil pertama, lalu pilih sel rumus, seret gagang isian ke sel yang ingin Anda isi rumus ini, lalu Anda akan mendapatkan hasil yang benar sesuai kebutuhan. Lihat tangkapan layar:

Catatan:

  • 1. Dalam rumus di atas, A2: A10 adalah kolom yang berisi nilai spesifik yang ingin Anda cari, F2 adalah nilai pencarian, C2: C10 adalah kolom di mana hasil akan dikembalikan.
  • 2. Jika ditemukan beberapa kecocokan, rumus ini akan selalu mengembalikan kecocokan pertama.

Rumus 2: Menggunakan fungsi Lookup dan Exact

1. Silakan terapkan rumus di bawah ini ke dalam sel kosong di mana Anda ingin mendapatkan hasilnya:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Kemudian, seret pegangan isi ke sel yang ingin Anda salin rumus ini, dan Anda akan mendapatkan nilai yang cocok dengan case sensitive seperti gambar di bawah ini:

Catatan:

  • 1. Dalam rumus di atas, A2: A10 adalah kolom yang berisi nilai spesifik yang ingin Anda cari, F2 adalah nilai pencarian, C2: C10 adalah kolom di mana hasil akan dikembalikan.
  • 2. Jika ditemukan beberapa kecocokan, rumus ini akan selalu mengembalikan kecocokan terakhir.

3. Nilai Vlookup dari kanan ke kiri di Excel

Fungsi Vlookup selalu mencari nilai di kolom paling kiri dari rentang data dan mengembalikan nilai yang sesuai dari kolom ke kanan. Jika Anda ingin melakukan Vlookup terbalik yang berarti mencari nilai tertentu di sebelah kanan dan mengembalikan nilai yang sesuai di kolom kiri seperti gambar di bawah ini:

Klik untuk mengetahui detail langkah demi langkah tentang tugas ini…


4. Vlookup nilai pencocokan kedua, n atau terakhir di Excel

Biasanya, jika ada beberapa nilai yang cocok ditemukan saat menggunakan fungsi Vlookup, hanya rekaman pertama yang cocok yang akan dikembalikan. Di bagian ini, saya akan berbicara tentang cara mendapatkan nilai pencocokan kedua, n atau terakhir dengan fungsi Vlookup.

Vlookup dan kembalikan nilai pencocokan kedua atau n

Misalkan Anda memiliki daftar nama di kolom A, kursus pelatihan yang mereka beli di kolom B, dan sekarang, Anda mencari untuk menemukan kursus pelatihan ke-2 atau ke-n yang dibeli oleh pelanggan tertentu. Lihat tangkapan layar:

1. Untuk mendapatkan nilai pencocokan kedua atau n berdasarkan kriteria yang diberikan, mohon terapkan rumus array berikut ke dalam sel kosong:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama, lalu pilih sel rumus, seret pegangan isian ke sel yang ingin Anda isi rumus ini, dan semua nilai kedua yang cocok berdasarkan nama yang diberikan telah ditampilkan sekaligus, lihat tangkapan layar:

Catatan:

  • Dalam formula ini, A2: A14 adalah rentang dengan semua nilai untuk pencarian, B2: B14 adalah kisaran nilai yang cocok yang ingin Anda kembalikan, E2 adalah nilai pencarian, dan angka terakhir 2 menunjukkan nilai pencocokan kedua yang ingin Anda dapatkan, jika Anda ingin mengembalikan nilai pencocokan ketiga, Anda hanya perlu mengubahnya menjadi 3 sesuai kebutuhan.

Vlookup dan kembalikan nilai pencocokan terakhir

Jika Anda ingin vlookup dan mengembalikan nilai pencocokan terakhir seperti gambar di bawah ini yang ditampilkan, ini Vlookup Dan Mengembalikan Nilai Pencocokan Terakhir tutorial dapat membantu Anda untuk mendapatkan nilai pencocokan terakhir secara rinci.


5. Vlookup mencocokkan nilai antara dua nilai atau tanggal yang diberikan

Terkadang, Anda mungkin ingin mencari nilai antara dua nilai atau tanggal dan mengembalikan hasil yang sesuai seperti gambar di bawah ini, dalam hal ini, Anda dapat menggunakan fungsi LOOKUP dan tabel yang diurutkan.

Vlookup mencocokkan nilai antara dua nilai atau tanggal yang diberikan dengan rumus

1. Pertama, tabel asli Anda harus berupa rentang data yang diurutkan. Dan kemudian, salin atau masukkan rumus berikut ke dalam sel kosong:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Kemudian, seret gagang isian untuk mengisi rumus ini ke sel lain yang Anda butuhkan, dan sekarang, Anda akan mendapatkan semua catatan yang cocok berdasarkan nilai yang diberikan, lihat tangkapan layar:

Catatan:

  • 1. Dalam rumus di atas, A2: A6 adalah kisaran nilai yang lebih kecil dan B2: B6 adalah kisaran angka yang lebih besar dalam rentang data Anda, yaitu E2 adalah nilai yang Anda inginkan untuk mendapatkan nilai yang sesuai, C2: C6 adalah data kolom yang ingin Anda ekstrak.
  • 2. Rumus ini juga dapat digunakan untuk mengekstraksi nilai yang cocok antara dua tanggal seperti gambar di bawah ini yang ditampilkan:

Vlookup mencocokkan nilai antara dua nilai atau tanggal yang diberikan dengan fitur yang berguna

Jika Anda merasa sakit dengan rumus di atas, di sini, saya akan memperkenalkan alat yang mudah - Kutools untuk Excel, Dengan yang LOOKUP antara Dua Nilai fitur, Anda dapat mengembalikan item yang sesuai berdasarkan nilai atau tanggal tertentu antara dua nilai atau tanggal tanpa mengingat rumus apa pun.   Klik untuk mengunduh Kutools for Excel sekarang!


6. Menggunakan wildcard untuk kecocokan parsial dalam fungsi Vlookup

Di Excel, karakter pengganti dapat digunakan dalam fungsi Vlookup, yang membuat pencocokan parsial pada nilai pencarian. Misalnya, Anda dapat menggunakan Vlookup untuk mengembalikan nilai yang cocok dari tabel berdasarkan bagian dari nilai pencarian.

Misalkan, saya memiliki berbagai data seperti gambar di bawah ini yang ditampilkan, sekarang, saya ingin mengekstrak skor berdasarkan nama depan (bukan nama lengkap). Bagaimana menyelesaikan tugas ini di Excel?

1. Fungsi Vlookup normal tidak berfungsi dengan benar, Anda perlu menggabungkan teks atau referensi sel dengan wildcard, harap salin atau masukkan rumus berikut ke dalam sel kosong:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2. Kemudian, seret pegangan isian untuk mengisi rumus ini ke sel lain yang Anda butuhkan, dan semua skor yang cocok telah dikembalikan seperti gambar di bawah ini:

Catatan:

  • 1. Dalam rumus di atas, E2 & ”*” adalah nilai pencarian, nilai dalam E2 dan * karakter pengganti ("*" menunjukkan salah satu karakter atau karakter apa pun), A2: C11 adalah rentang pencarian, jumlahnya 3 kolom yang berisi nilai untuk dikembalikan.
  • 2. Vlookup saat menggunakan wildcard, Anda harus menyetel mode pencocokan tepat dengan FALSE atau 0 untuk argumen terakhir dalam fungsi Vlookup.

Tip:

1. Temukan dan kembalikan nilai yang cocok yang diakhiri dengan nilai tertentu, harap terapkan rumus ini: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Untuk mencari dan mengembalikan nilai yang cocok berdasarkan bagian dari string teks, apakah teks yang ditentukan berada di depan, di belakang atau di tengah string teks, Anda hanya perlu menggabungkan dua karakter * di sekitar referensi sel atau teks. Silakan lakukan dengan rumus ini: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Nilai Vlookup dari lembar kerja lain

Biasanya, Anda mungkin harus bekerja dengan lebih dari satu lembar kerja, fungsi Vlookup dapat digunakan untuk mencari data dari lembar lain sama seperti pada satu lembar kerja.

Misalnya, Anda memiliki dua lembar kerja seperti gambar di bawah ini yang ditampilkan, untuk mencari dan mengembalikan data yang sesuai dari lembar kerja yang Anda tentukan, lakukan dengan langkah-langkah berikut:

1. Silakan masukkan atau salin rumus di bawah ini ke dalam sel kosong di mana Anda ingin mendapatkan item yang cocok:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. Kemudian, seret gagang isian ke sel yang ingin Anda terapkan rumus ini, dan Anda akan mendapatkan hasil yang sesuai sesuai kebutuhan, lihat tangkapan layar:

Catatan: Dalam rumus di atas:

  • A2 mewakili nilai pencarian;
  • Lembaran data adalah nama lembar kerja yang ingin Anda cari datanya, (Jika nama lembar berisi spasi atau karakter tanda baca, Anda harus mengapit tanda kutip tunggal di sekitar nama lembar, jika tidak, Anda dapat langsung menggunakan nama lembar seperti = VLOOKUP (A2, Lembar Data! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 adalah kisaran data di Lembar Data tempat kita mencari data;
  • nomor 3 adalah nomor kolom yang berisi data cocok yang ingin Anda kembalikan.

8. Nilai Vlookup dari buku kerja lain

Bagian ini akan membahas tentang pencarian dan mengembalikan nilai yang cocok dari buku kerja yang berbeda dengan menggunakan fungsi Vlookup.

Misalnya, buku kerja pertama berisi daftar produk dan biaya, sekarang, Anda ingin mengekstrak biaya terkait di buku kerja kedua berdasarkan item produk seperti gambar di bawah ini.

1. Untuk mengambil biaya relatif dari buku kerja lain, pertama, buka kedua buku kerja yang ingin Anda gunakan, lalu terapkan rumus berikut ke dalam sel tempat Anda ingin meletakkan hasilnya:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2. Kemudian, seret dan salin rumus ini ke sel lain yang Anda butuhkan, lihat tangkapan layar:

Catatan:

  • 1. Dalam rumus di atas:
    B2 mewakili nilai pencarian;
    [Produk list.xlsx] Sheet1 adalah nama buku kerja dan lembar kerja yang datanya ingin Anda cari, (Referensi ke buku kerja diapit tanda kurung siku, dan seluruh + lembar buku kerja diapit tanda kutip tunggal);
    A2: B6 adalah kisaran data di lembar kerja buku kerja lain tempat kami mencari datanya;
    nomor 2 adalah nomor kolom yang berisi data cocok yang ingin Anda kembalikan.
  • 2. Jika buku kerja pencarian ditutup, jalur file lengkap untuk buku kerja pencarian akan ditampilkan dalam rumus seperti gambar berikut yang ditampilkan:

9. Vlookup dan kembalikan teks kosong atau spesifik alih-alih nilai kesalahan 0 atau # N / A

Biasanya, ketika Anda menerapkan fungsi vlookup untuk mengembalikan nilai yang sesuai, jika sel yang cocok Anda kosong, itu akan mengembalikan 0, dan jika nilai yang cocok Anda tidak ditemukan, Anda akan mendapatkan kesalahan # N / A nilai seperti gambar di bawah ini. Alih-alih menampilkan nilai 0 atau # N / A dengan sel kosong atau nilai lain yang Anda suka, ini Vlookup Untuk Mengembalikan Nilai Kosong Atau Tertentu Daripada 0 Atau N / A tutorial dapat membantu Anda selangkah demi selangkah.


Contoh VLOOKUP lanjutan

1. Pencarian dua arah dengan fungsi Vlookup (Vlookup di baris dan kolom)

Terkadang, Anda mungkin perlu melakukan pencarian 2 dimensi, yang berarti Vlookup di baris dan kolom pada saat yang bersamaan. Katakanlah, jika Anda memiliki rentang data berikut, dan sekarang, Anda mungkin perlu mendapatkan nilai untuk produk tertentu dalam kuartal tertentu. Bagian ini akan memperkenalkan beberapa rumus untuk menangani pekerjaan ini di Excel.

Rumus 1: Menggunakan fungsi VLOOKUP dan MATCH

Di Excel, Anda bisa menggunakan kombinasi fungsi VLOOKUP dan MATCH untuk melakukan pencarian dua arah, terapkan rumus berikut ke dalam sel kosong, lalu tekan Memasukkan kunci untuk mendapatkan hasil.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Catatan: Dalam rumus di atas:

  • H1: nilai pencarian di kolom tempat Anda ingin mendapatkan nilai yang sesuai;
  • A2: E6: kisaran data termasuk header baris;
  • H2: nilai pencarian di baris yang Anda inginkan untuk mendapatkan nilai yang sesuai;
  • A1: E1: sel header kolom.

Rumus 2: Menggunakan fungsi INDEX dan MATCH

Berikut adalah rumus lain yang juga dapat membantu Anda melakukan pencarian 2 dimensi, terapkan rumus di bawah ini, lalu tekan Memasukkan kunci untuk mendapatkan hasil yang Anda butuhkan.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Catatan: Dalam rumus di atas:

  • B2: E6: rentang data untuk mengembalikan item yang cocok;
  • H1: nilai pencarian di kolom tempat Anda ingin mendapatkan nilai yang sesuai;
  • A2: A6: tajuk baris berisi produk yang ingin Anda cari.
  • H2: nilai pencarian di baris yang Anda inginkan untuk mendapatkan nilai yang sesuai;
  • B1: E1: tajuk kolom berisi kuartal yang ingin Anda cari.

2. Nilai pencocokan Vlookup berdasarkan dua kriteria atau lebih

Mudah bagi Anda untuk mencari nilai pencocokan berdasarkan satu kriteria, tetapi jika Anda memiliki dua kriteria atau lebih, apa yang dapat Anda lakukan? Fungsi LOOKUP atau MATCH dan INDEX di Excel dapat membantu Anda menyelesaikan pekerjaan ini dengan cepat dan mudah.

Misalnya, saya memiliki tabel data di bawah ini, untuk mengembalikan harga yang cocok berdasarkan produk dan ukuran tertentu, rumus berikut dapat membantu Anda.

Rumus 1: Menggunakan fungsi LOOKUP

Silakan terapkan rumus di bawah ini ke dalam sel di mana Anda ingin mendapatkan hasilnya, lalu tekan Memasukkan kunci, lihat tangkapan layar:

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Catatan:

  • 1. Dalam rumus di atas:
    A2: A12 = G1: artinya mencari kriteria G1 dalam range A2: A12;
    B2: B12 = G2: cara mencari kriteria G2 di range B2: B12;
    D2: D12: rentang yang ingin Anda kembalikan nilai yang sesuai.
  • 2. Jika Anda memiliki lebih dari dua kriteria, Anda hanya perlu menggabungkan kriteria lain ke dalam rumus, seperti: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Rumus 2: Menggunakan fungsi INDEXT dan MATCH

Kombinasi fungsi Index dan Match juga dapat digunakan untuk mengembalikan nilai yang cocok berdasarkan beberapa kriteria. Silakan salin atau masukkan rumus berikut:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Kemudian, tekan Ctrl + Shift + Enter bersamaan untuk mendapatkan nilai relatif yang Anda butuhkan. Lihat tangkapan layar:

Catatan:

  • 1. Dalam rumus di atas:
    A2: A12 = G1: artinya mencari kriteria G1 dalam range A2: A12;
    B2: B12 = G2: cara mencari kriteria G2 di range B2: B12;
    D2: D12: rentang yang ingin Anda kembalikan nilai yang sesuai.
  • 2. Jika Anda memiliki lebih dari dua kriteria, Anda hanya perlu menggabungkan kriteria baru ke dalam rumus, seperti: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup untuk mengembalikan beberapa nilai yang cocok dengan satu atau lebih kondisi

Di Excel, fungsi Vlookup mencari nilai dan hanya mengembalikan nilai pertama yang cocok jika terdapat beberapa nilai yang sesuai. Terkadang, Anda mungkin ingin mengembalikan semua nilai terkait dalam satu baris, dalam satu kolom atau dalam satu sel. Bagian ini akan membahas tentang cara mengembalikan beberapa nilai yang cocok dengan satu atau beberapa kondisi dalam buku kerja.

Vlookup semua nilai yang cocok berdasarkan satu atau lebih kondisi secara horizontal

Vlookup semua nilai yang cocok berdasarkan satu kondisi secara horizontal:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan satu nilai tertentu secara horizontal, rumus generiknya adalah:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Catatan: m adalah nomor baris dari sel pertama dalam rentang pengembalian dikurangi 1.
      n adalah nomor kolom dari sel formula pertama dikurangi 1.

1. Harap terapkan rumus di bawah ini ke dalam sel kosong, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama yang cocok, lihat tangkapan layar:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Dan kemudian, pilih sel formula pertama, dan seret gagang isian ke sel kanan sampai sel kosong ditampilkan, dan semua item terkait telah diekstraksi, lihat tangkapan layar:

Tip:

Jika ada nilai duplikat yang cocok dalam daftar yang dikembalikan, untuk mengabaikan duplikat, gunakan rumus ini, lalu tekan Memasukkan untuk mendapatkan hasil pertama: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Lanjutkan memasukkan rumus ini: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") ke dalam sel di samping hasil pertama, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil kedua, lalu seret rumus ini ke sel kanan untuk mendapatkan semua nilai lain yang cocok sampai sel kosong ditampilkan, lihat tangkapan layar:


Vlookup semua nilai yang cocok berdasarkan dua atau lebih kondisi secara horizontal:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan nilai yang lebih spesifik secara horizontal, rumus generiknya adalah:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Catatan: m adalah nomor baris dari sel pertama dalam rentang pengembalian dikurangi 1.
      n adalah nomor kolom dari sel formula pertama dikurangi 1.

1. Terapkan rumus berikut ke dalam sel kosong tempat Anda ingin mengeluarkan hasilnya:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Kemudian, pilih sel formula dan seret gagang isian ke sel kanan sampai sel kosong ditampilkan, dan semua nilai yang cocok berdasarkan kriteria tertentu telah dikembalikan, lihat tangkapan layar:

Catatan: Untuk kriteria lainnya, Anda hanya perlu menggabungkan lookup_value dan lookup_range ke dalam rumus, seperti: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Vlookup semua nilai yang cocok berdasarkan satu atau lebih kondisi secara vertikal

Vlookup semua nilai yang cocok berdasarkan satu kondisi secara vertikal:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan satu nilai tertentu secara vertikal, rumus generiknya adalah:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Catatan: m adalah nomor baris dari sel pertama dalam rentang pengembalian dikurangi 1.
      n adalah nomor baris dari sel rumus pertama dikurangi 1.

1. Salin atau ketikkan rumus berikut ke dalam sel tempat Anda ingin mendapatkan hasilnya, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama yang cocok, lihat tangkapan layar:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Kemudian, pilih sel formula pertama, dan seret gagang isian ke bawah ke sel lain sampai sel kosong ditampilkan, dan semua item yang sesuai telah terdaftar di kolom, lihat tangkapan layar:

Tip:

Untuk mengabaikan duplikat dalam nilai yang cocok yang dikembalikan, gunakan rumus ini: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama yang cocok, lalu seret sel rumus ini ke bawah ke sel lain hingga sel kosong ditampilkan, dan Anda akan mendapatkan hasil sesuai kebutuhan:


Vlookup semua nilai yang cocok berdasarkan dua atau lebih kondisi secara vertikal:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan nilai yang lebih spesifik secara vertikal, rumus generiknya adalah:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Catatan: m adalah nomor baris dari sel pertama dalam rentang pengembalian dikurangi 1.
      n adalah nomor baris dari sel rumus pertama dikurangi 1.

1. Salin rumus di bawah ini ke dalam sel kosong, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan item pertama yang cocok.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Kemudian seret sel formula ke sel lain sampai sel kosong ditampilkan, lihat tangkapan layar:

Catatan: Untuk kriteria lainnya, Anda hanya perlu menggabungkan lookup_value dan lookup_range ke dalam rumus, seperti: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup semua nilai yang cocok berdasarkan dua atau lebih kondisi ke dalam sel tunggal

Jika Anda ingin Vlookup dan mengembalikan beberapa nilai yang cocok ke dalam satu sel dengan pemisah yang ditentukan, fungsi baru TEXTJOIN dapat membantu Anda menyelesaikan pekerjaan ini dengan cepat dan mudah.

Vlookup semua nilai yang cocok berdasarkan satu kondisi ke dalam sel tunggal:

Harap terapkan rumus sederhana di bawah ini ke dalam sel kosong, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Tip:

Untuk mengabaikan duplikat dalam nilai yang cocok yang dikembalikan, gunakan rumus ini: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup semua nilai yang cocok berdasarkan dua atau lebih kondisi ke dalam sel tunggal:

Untuk menangani beberapa kondisi saat mengembalikan semua nilai yang cocok ke dalam satu sel, terapkan rumus di bawah ini, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Catatan:

1. Fungsi TEXTJOIN hanya tersedia di Excel 2019 dan Office 365.

2. Jika Anda menggunakan Excel 2016 dan versi sebelumnya, harap gunakan Fungsi Buatan Pengguna dari artikel di bawah ini:


4. Vlookup untuk mengembalikan seluruh atau seluruh baris dari sel yang cocok

Di bagian ini, saya akan berbicara tentang cara mengambil seluruh baris dari nilai yang cocok dengan menggunakan fungsi Vlookup.

1. Silakan salin atau ketik rumus di bawah ini ke dalam sel kosong di mana Anda ingin menampilkan hasilnya, dan tekan Memasukkan kunci untuk mendapatkan nilai pertama.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2. Kemudian, seret sel formula ke sisi kanan hingga data seluruh baris ditampilkan, lihat tangkapan layar:

Catatan: Dalam rumus di atas, F2 adalah nilai pencarian yang ingin Anda kembalikan ke seluruh baris, A1: D12 adalah rentang data yang ingin Anda gunakan, A1 menunjukkan nomor kolom pertama dalam rentang data Anda.

Tip:

Jika beberapa baris ditemukan berdasarkan nilai yang cocok, untuk mengembalikan semua baris yang sesuai, terapkan rumus ini: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama, lalu seret gagang isian langsung ke sel, lihat tangkapan layar:

Dan kemudian seret gagang isian ke bawah melintasi sel untuk mendapatkan semua baris yang cocok seperti gambar di bawah ini yang ditampilkan:


5. Lakukan beberapa fungsi Vlookup (Vlookup bersarang) di Excel

Terkadang, Anda mungkin ingin mencari nilai dalam beberapa tabel, jika salah satu tabel berisi nilai pencarian yang diberikan seperti gambar di bawah ini, dalam hal ini, Anda dapat menggabungkan satu atau lebih fungsi Vlookup bersama dengan fungsi IFERROR untuk melakukan pencarian ganda.

Rumus umum untuk fungsi Vlookup bersarang adalah:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Catatan:

  • nilai lookup: nilai yang Anda cari;
  • Table1, Table2, Table3, ...: tabel di mana nilai pencarian dan nilai kembali ada;
  • col: nomor kolom dalam tabel yang ingin Anda kembalikan nilai yang cocok.
  • 0: Ini digunakan untuk pencocokan tepat.

1. Harap terapkan rumus berikut ke dalam sel kosong di mana Anda ingin meletakkan hasilnya:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Kemudian, seret pegangan isi ke sel yang ingin Anda terapkan rumus ini, dan semua nilai yang cocok telah dikembalikan seperti gambar di bawah ini:

Catatan:

  • 1. Dalam rumus di atas, J3 adalah nilai yang Anda cari; A3: B7, D3: E7, G3: H7 adalah rentang tabel tempat nilai pencarian dan nilai kembalian ada; Nomor 2 adalah nomor kolom dalam rentang untuk mengembalikan nilai yang cocok.
  • 2. Jika nilai pencarian tidak dapat ditemukan, nilai kesalahan ditampilkan, untuk mengganti kesalahan dengan teks yang dapat dibaca, gunakan rumus ini: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup untuk memeriksa apakah ada nilai berdasarkan data daftar di kolom lain

Fungsi Vlookup juga dapat membantu Anda untuk memeriksa apakah ada nilai berdasarkan daftar lain, misalnya jika Anda ingin mencari nama di kolom C dan hanya mengembalikan Ya atau Tidak jika nama ditemukan atau tidak di kolom A seperti gambar di bawah ini ditampilkan.

1. Harap terapkan rumus berikut ke dalam sel kosong:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2. Kemudian, seret pegangan isi ke sel yang ingin Anda isi rumus ini, dan Anda akan mendapatkan hasil yang Anda butuhkan, lihat tangkapan layar:

Catatan: Dalam rumus di atas, C2 adalah nilai pencarian yang ingin Anda periksa; A2: A10 adalah daftar rentang dari mana nilai pencarian akan ditemukan; nomor 1 adalah nomor kolom dari mana Anda ingin mengambil nilai dalam rentang Anda.


7. Vlookup dan jumlahkan semua nilai yang cocok dalam baris atau kolom

Jika Anda bekerja dengan data numerik, terkadang, saat mengekstrak nilai yang cocok dari tabel, Anda mungkin juga perlu menjumlahkan angka dalam beberapa kolom atau baris. Bagian ini akan memperkenalkan beberapa rumus untuk menyelesaikan pekerjaan ini di Excel.

Vlookup dan jumlahkan semua nilai yang cocok dalam satu baris atau beberapa baris

Misalkan, Anda memiliki daftar produk dengan penjualan selama beberapa bulan seperti gambar di bawah ini, sekarang, Anda perlu menjumlahkan semua pesanan dalam semua bulan berdasarkan produk yang diberikan.

Vlookup dan jumlahkan nilai pertama yang cocok berturut-turut:

1. Harap salin atau masukkan rumus berikut ke dalam sel kosong, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2. Kemudian, seret gagang isian ke bawah untuk menyalin rumus ini ke sel lain yang Anda butuhkan, dan semua nilai dalam baris dari nilai pertama yang cocok telah dijumlahkan, lihat tangkapan layar:

Catatan: Dalam rumus di atas: H2 adalah sel yang berisi nilai yang Anda cari; A2: F9 adalah rentang data (tanpa tajuk kolom) yang menyertakan nilai pencarian dan nilai yang cocok; Nomor 2,3,4,5,6 {} adalah nomor kolom yang digunakan untuk menghitung total rentang.


Vlookup dan jumlahkan semua nilai yang cocok dalam beberapa baris:

Rumus di atas hanya dapat menjumlahkan nilai dalam satu baris untuk nilai pertama yang cocok. Jika Anda ingin menjumlahkan semua kecocokan dalam beberapa baris, gunakan rumus berikut, lalu seret gagang isian ke sel yang ingin Anda terapkan rumus ini, dan Anda akan mendapatkan hasil yang diinginkan, lihat tangkapan layar:

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

Catatan: Dalam rumus di atas: H2 adalah nilai pencarian yang Anda cari; A2: A9 adalah header baris yang berisi nilai pencarian; B2: F9 rentang data dari nilai numerik yang ingin Anda jumlahkan.


Vlookup dan jumlahkan semua nilai yang cocok dalam satu kolom atau beberapa kolom

Vlookup dan jumlahkan nilai pertama yang cocok di kolom:

Jika Anda ingin menjumlahkan nilai total untuk bulan tertentu seperti yang ditunjukkan pada gambar di bawah.

Terapkan rumus di bawah ini ke dalam sel kosong, lalu seret gagang isian ke bawah untuk menyalin rumus ini ke sel lain, sekarang, nilai pertama yang cocok berdasarkan bulan tertentu di kolom telah dijumlahkan, lihat tangkapan layar:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Catatan: Dalam rumus di atas: H2 adalah nilai pencarian yang Anda cari; B1: F1 adalah tajuk kolom yang berisi nilai pencarian; B2: F9 rentang data dari nilai numerik yang ingin Anda jumlahkan.


Vlookup dan jumlahkan semua nilai yang cocok dalam beberapa kolom:

Untuk Vlookup dan menjumlahkan semua nilai yang cocok dalam beberapa kolom, Anda harus menggunakan rumus berikut:

=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))

Catatan: Dalam rumus di atas: H2 adalah nilai pencarian yang Anda cari; B1: F1 adalah tajuk kolom yang berisi nilai pencarian; B2: F9 rentang data dari nilai numerik yang ingin Anda jumlahkan.


Vlookup dan jumlahkan nilai pertama yang cocok atau semua yang cocok dengan fitur yang kuat

Mungkin rumus di atas sulit untuk Anda ingat, dalam hal ini, saya akan merekomendasikan fitur yang berguna - Pencarian dan Jumlah of Kutools untuk Excel, dengan fitur ini, Anda bisa mendapatkan hasil semudah mungkin.    Klik untuk mengunduh Kutools for Excel sekarang!


Vlookup dan jumlahkan semua nilai yang cocok baik dalam baris dan kolom

Jika Anda ingin menjumlahkan nilai saat Anda perlu mencocokkan kolom dan baris, misalnya, untuk mendapatkan nilai total produk Sweater di bulan Mar seperti gambar di bawah ini.

Silakan terapkan rumus berikut ke dalam sel, lalu tekan Memasukkan kunci untuk mendapatkan hasilnya, lihat tangkapan layar:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Catatan: Dalam rumus di atas: B2: F9 adalah rentang data dari nilai numerik yang ingin Anda jumlahkan; B1: F1 is header kolom berisi nilai pencarian yang ingin Anda jumlahkan berdasarkan; I2 adalah nilai pencarian dalam tajuk kolom yang Anda cari; A2: A9 adalah header baris berisi nilai pencarian yang ingin Anda jumlahkan berdasarkan; H2 adalah nilai pencarian dalam tajuk baris yang Anda cari.


8. Vlookup untuk menggabungkan dua tabel berdasarkan satu atau beberapa kolom kunci

Dalam pekerjaan sehari-hari Anda, saat menganalisis data, Anda mungkin perlu mengumpulkan semua informasi yang diperlukan ke dalam satu tabel berdasarkan satu atau lebih kolom kunci. Untuk menyelesaikan pekerjaan ini, fungsi Vlookup juga dapat membantu Anda.

Vlookup untuk menggabungkan dua tabel berdasarkan satu kolom kunci

Misalnya, Anda memiliki dua tabel, tabel pertama berisi data produk dan nama, dan tabel kedua berisi produk dan pesanan, sekarang, Anda ingin menggabungkan dua tabel ini dengan mencocokkan kolom produk umum ke dalam satu tabel.

Rumus 1: Menggunakan fungsi VLOOKUP

Untuk menggabungkan dua tabel menjadi satu berdasarkan kolom kunci, harap terapkan rumus berikut ke dalam sel kosong tempat Anda ingin mendapatkan hasilnya, lalu seret gagang isian ke sel yang ingin Anda terapkan rumus ini, Anda akan dapatkan tabel yang digabungkan dengan kolom pesanan yang bergabung dengan data tabel pertama berdasarkan data kolom kunci.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Catatan: Dalam rumus di atas, A2 adalah nilai yang Anda cari, E2: F8 adalah tabel yang akan dicari, nomornya 2 adalah nomor kolom dalam tabel yang akan diambil nilainya.

Rumus 2: Menggunakan fungsi INDEX dan MATCH

Jika data umum Anda di sisi kanan dan data yang dikembalikan di kolom kiri dalam tabel kedua, untuk menggabungkan kolom pesanan, fungsi Vlookup tidak dapat melakukan pekerjaan itu. Untuk mencari dari kanan ke kiri, Anda dapat menggunakan fungsi INDEX dan MATCH untuk menggantikan fungsi Vlookup.

Silakan salin atau masukkan rumus di bawah ini ke dalam sel kosong, lalu salin rumus ke bawah kolom, dan kolom urutan telah digabungkan ke tabel pertama, lihat tangkapan layar:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

Catatan: Dalam rumus di atas, A2 adalah nilai pencarian yang Anda cari, E2: E8 adalah rentang data yang ingin Anda kembalikan, F2: F8 adalah rentang pencarian yang berisi nilai pencarian.


Vlookup untuk menggabungkan dua tabel berdasarkan beberapa kolom kunci

Jika dua tabel yang ingin Anda gabungkan memiliki beberapa kolom kunci, untuk menggabungkan tabel berdasarkan kolom umum ini, fungsi INDEX dan MATCH dapat membantu Anda.

Rumus umum untuk menggabungkan dua tabel berdasarkan beberapa kolom kunci adalah:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Harap terapkan rumus di bawah ini ke dalam sel kosong tempat Anda ingin meletakkan hasilnya, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama yang cocok, lihat tangkapan layar:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Catatan: Dalam rumus di atas, apa yang direpresentasikan oleh referensi sel seperti gambar di bawah ini:

2Kemudian, pilih sel rumus pertama, dan seret gagang isian untuk menyalin rumus ini ke sel lain yang Anda butuhkan:

Tips: Di Excel 2016 dan versi yang lebih baru, Anda juga bisa menggunakan Kueri Daya fitur untuk menggabungkan dua atau lebih tabel menjadi satu berdasarkan kolom kunci. Silakan klik untuk mengetahui detailnya selangkah demi selangkah.

9. Vlookup mencocokkan nilai di beberapa lembar kerja

Pernahkah Anda mencoba Vlookup nilai di beberapa lembar kerja? Misalkan saya memiliki tiga lembar kerja berikut dengan berbagai data, dan sekarang, saya ingin mendapatkan bagian dari nilai yang sesuai berdasarkan kriteria dari ketiga lembar kerja ini untuk mendapatkan hasil seperti gambar di bawah ini. Dalam hal ini, file Nilai Vlookup di Beberapa Lembar Kerja tutorial dapat membantu Anda selangkah demi selangkah.


VLOOKUP cocok nilai mempertahankan pemformatan sel

1. Vlookup untuk mendapatkan pemformatan sel (warna sel, warna font) bersama dengan nilai pencarian

Seperti yang kita semua tahu, fungsi Vlookup normal hanya dapat membantu kita mengembalikan nilai yang cocok dari rentang data lain, tetapi terkadang, Anda mungkin ingin mengembalikan nilai yang sesuai bersama dengan pemformatan sel, seperti warna isian, warna font, gaya font seperti gambar di bawah ini. Bagian ini akan berbicara tentang cara mendapatkan pemformatan sel dengan nilai yang dikembalikan di Excel.

Harap lakukan dengan langkah-langkah berikut untuk mencari dan mengembalikan nilai yang sesuai bersama dengan pemformatan sel:

1. Di lembar kerja berisi data yang ingin Anda Vlookup, klik kanan tab lembar dan pilih Lihat kode dari menu konteks. Lihat tangkapan layar:

2. Di tempat terbuka Microsoft Visual Basic untuk Aplikasi jendela, harap salin kode VBA di bawah ini ke jendela Kode.

Kode VBA 1: Vlookup untuk mendapatkan pemformatan sel bersama dengan nilai pencarian

Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
                Range(xDic.Keys(I)).Font.FontStyle = _
                Range(xDic.Items(I)).Font.FontStyle
                Range(xDic.Keys(I)).Font.Size = _
                Range(xDic.Items(I)).Font.Size
                Range(xDic.Keys(I)).Font.Color = _
                Range(xDic.Items(I)).Font.Color
                Range(xDic.Keys(I)).Font.Name = _
                Range(xDic.Items(I)).Font.Name
                Range(xDic.Keys(I)).Font.Underline = _
                Range(xDic.Items(I)).Font.Underline
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. Masih di Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu salin kode VBA 2 di bawah ini ke jendela Modul.

Kode VBA 2: Vlookup untuk mendapatkan pemformatan sel bersama dengan nilai pencarian

Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. Setelah memasukkan kode diatas, selanjutnya klik alat > Referensi dalam Microsoft Visual Basic untuk Aplikasi jendela. Kemudian periksa Waktu Proses Skrip Microsoft kotak centang di Referensi - VBAProject kotak dialog. Lihat tangkapan layar:

5. Lalu klik OK untuk menutup kotak dialog, lalu simpan dan tutup jendela kode, sekarang, kembali ke lembar kerja, lalu terapkan rumus ini: =LookupKeepFormat(E2,$A$1:$C$10,3) ke dalam sel kosong tempat Anda ingin mengeluarkan hasilnya, lalu tekan tombol Enter. Lihat tangkapan layar:

Catatan: Dalam rumus di atas, E2 adalah nilai yang akan kamu cari, A1: C10 adalah rentang tabel, dan angka 3 adalah nomor kolom dari tabel yang Anda ingin nilai yang cocok dikembalikan.

6. Kemudian, pilih sel hasil pertama, dan seret gagang isian ke bawah untuk mendapatkan semua hasil beserta pemformatannya. Lihat tangkapan layar.


2. Simpan format tanggal dari nilai yang dikembalikan Vlookup

Biasanya, saat menggunakan fungsi Vloook untuk mencari dan mengembalikan nilai format tanggal yang cocok, beberapa format angka akan ditampilkan seperti gambar di bawah ini. Untuk menjaga format tanggal dari hasil yang dikembalikan, Anda harus menyertakan fungsi TEXT ke fungsi Vlookup.

Harap terapkan rumus di bawah ini ke dalam sel kosong, lalu seret pegangan isian untuk menyalin rumus ini ke sel lain, dan semua tanggal yang cocok telah dikembalikan seperti gambar di bawah ini:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Catatan: Dalam rumus di atas, E2 adalah nilai tampilan, A2: C9 adalah rentang pencarian, jumlahnya 3 adalah nomor kolom yang Anda inginkan nilainya dikembalikan, bb / hh / tttt adalah format tanggal yang ingin Anda pertahankan.


3. Vlookup dan mengembalikan nilai yang cocok dengan komentar sel

Pernahkah Anda mencoba Vlookup untuk mengembalikan tidak hanya data sel yang cocok, tetapi juga komentar sel juga di Excel seperti gambar berikut yang ditampilkan? Untuk mengatasi tugas ini, Fungsi Buatan Pengguna di bawah ini dapat membantu Anda.

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, lalu salin dan tempel kode berikut di Jendela Modul.

Kode VBA: Vlookup dan mengembalikan nilai yang cocok dengan komentar sel:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. Kemudian simpan dan tutup jendela kode, masukkan rumus ini: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) ke dalam sel kosong untuk menemukan hasilnya, lalu seret gagang isian untuk menyalin rumus ini ke sel lain, sekarang, nilai yang cocok serta komentar dikembalikan sekaligus, lihat tangkapan layar:

Catatan: Dalam rumus di atas, D2 adalah nilai pencarian yang ingin Anda kembalikan nilainya yang sesuai, A2: B9 adalah tabel data yang ingin Anda gunakan, nomornya 2 adalah nomor kolom yang berisi nilai cocok yang ingin Anda kembalikan.


4. Tangani teks dan bilangan real di Vlookup

Misalnya, saya memiliki berbagai data, nomor ID di tabel asli adalah format angka, di sel pencarian yang disimpan sebagai teks, saat menerapkan fungsi Vlookup normal, hasil kesalahan # N / A ditampilkan seperti gambar di bawah ini ditampilkan. Dalam kasus ini, bagaimana Anda bisa mendapatkan informasi yang benar jika nomor pencarian dan nomor asli dalam tabel memiliki format data yang berbeda?

Untuk menangani teks dan bilangan real dalam fungsi Vlookup, silakan terapkan rumus berikut ke dalam sel kosong, lalu seret pegangan isian ke bawah untuk menyalin rumus ini, dan Anda akan mendapatkan hasil yang benar seperti gambar di bawah ini:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Catatan:

  • 1. Dalam rumus di atas, D2 adalah nilai pencarian yang ingin Anda kembalikan nilainya yang sesuai, A2: B8 adalah tabel data yang ingin Anda gunakan, nomornya 2 adalah nomor kolom yang berisi nilai cocok yang ingin Anda kembalikan.
  • 2. Rumus ini juga berfungsi dengan baik jika Anda tidak yakin di mana Anda memiliki angka dan di mana Anda memiliki teks.

Unduh file contoh VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula Bar (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...
tab kte 201905
  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.