Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara vlookup dan mengembalikan beberapa nilai secara vertikal di Excel?

Biasanya, Anda dapat menggunakan fungsi Vlookup untuk mendapatkan nilai pertama yang sesuai, tetapi terkadang Anda ingin mengembalikan semua rekaman yang cocok berdasarkan kriteria tertentu. Artikel ini, saya akan berbicara tentang cara vlookup dan mengembalikan semua nilai yang cocok secara vertikal, horizontal atau ke dalam satu sel.

Vlookup dan mengembalikan semua nilai yang sesuai secara vertikal

Vlookup dan mengembalikan semua nilai yang sesuai secara horizontal

Vlookup dan mengembalikan semua nilai yang sesuai ke dalam satu sel


Vlookup dan mengembalikan semua nilai yang sesuai secara vertikal

Untuk mengembalikan semua nilai yang cocok secara vertikal berdasarkan kriteria tertentu, harap terapkan rumus array berikut:

1. Masukkan atau salin rumus ini ke dalam sel kosong tempat Anda ingin menampilkan hasilnya:

=IFERROR(INDEX($B$2:$B$15, SMALL(IF($D$2=$A$2:$A$15, ROW($A$2:$A$15)-ROW($A$2)+1), ROW(1:1))),"" )

Note: Dalam rumus di atas, B2: B15 adalah kolom berisi rekaman yang cocok yang ingin Anda kembalikan; A2: A15 adalah kolom berisi kriteria; dan D2 adalah kriteria spesifik yang ingin Anda kembalikan nilai-nilainya. Harap ubah sesuai kebutuhan Anda.

2. Lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama, lalu seret gagang isian ke bawah untuk mendapatkan semua rekaman yang sesuai yang Anda butuhkan, lihat tangkapan layar:


Vlookup dan mengembalikan semua nilai yang sesuai secara horizontal

Jika Anda ingin menampilkan nilai yang cocok dalam urutan horizontal, rumus array di bawah ini dapat membantu Anda.

1. Masukkan atau salin rumus ini ke dalam sel kosong tempat Anda ingin menampilkan hasilnya:

=IFERROR(INDEX($B$2:$B$15,SMALL(IF($E$1=$A$2:$A$15,ROW($A$2:$A$15)-ROW($A$2)+1),COLUMN(A1))),"")

Note: Dalam rumus di atas, B2: B15 adalah kolom berisi rekaman yang cocok yang ingin Anda kembalikan; A2: A15 adalah kolom berisi kriteria; dan D2 adalah kriteria spesifik yang ingin Anda kembalikan nilai-nilainya. Harap ubah sesuai kebutuhan Anda.

2. Lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama, lalu seret gagang isian ke kanan untuk mendapatkan semua catatan yang sesuai yang Anda butuhkan, lihat tangkapan layar:


Vlookup dan mengembalikan semua nilai yang sesuai ke dalam satu sel

Untuk vlookup dan mengembalikan semua nilai terkait ke dalam satu sel, Anda harus menerapkan rumus array berikut.

1. Masukkan atau salin rumus di bawah ini ke dalam sel kosong:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))

Note: Dalam rumus di atas, B2: B15 adalah kolom berisi rekaman yang cocok yang ingin Anda kembalikan; A2: A15 adalah kolom berisi kriteria; dan D2 adalah kriteria spesifik yang ingin Anda kembalikan nilai-nilainya. Harap ubah sesuai kebutuhan Anda.

2. Lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan semua nilai yang cocok ke dalam satu sel, lihat tangkapan layar:

Tip: Rumus ini hanya berhasil diterapkan di Excel 2016 dan versi yang lebih baru. Jika Anda tidak memiliki Excel 2016, silakan lihat disini untuk menurunkannya.


Artikel Vlookup lainnya:

  • Vlookup Dan Kembalikan Beberapa Nilai Dari Daftar Drop Down
  • Di Excel, bagaimana Anda bisa vlookup dan mengembalikan beberapa nilai yang sesuai dari daftar drop-down, yang berarti ketika Anda memilih satu item dari daftar drop-down, semua nilai relatifnya ditampilkan sekaligus seperti gambar berikut yang ditampilkan. Artikel ini, saya akan memperkenalkan solusi langkah demi langkah.
  • Vlookup Untuk Mengembalikan Kosong Bukan 0 Atau N / A Di Excel
  • Biasanya, saat Anda menerapkan fungsi vlookup untuk mengembalikan nilai yang sesuai, jika sel yang cocok Anda kosong, itu akan mengembalikan 0, dan jika nilai yang cocok tidak ditemukan, Anda akan mendapatkan kesalahan # N / A nilai. Alih-alih menampilkan nilai 0 atau # N / A, bagaimana Anda bisa membuatnya memperlihatkan sel kosong?
  • Vlookup Untuk Mengembalikan Beberapa Kolom Dari Tabel Excel
  • Di lembar kerja Excel, Anda dapat menerapkan fungsi Vlookup untuk mengembalikan nilai yang cocok dari satu kolom. Namun, terkadang, Anda mungkin perlu mengekstrak nilai yang cocok dari beberapa kolom seperti gambar berikut yang ditampilkan. Bagaimana Anda bisa mendapatkan nilai yang sesuai pada saat yang sama dari beberapa kolom dengan menggunakan fungsi Vlookup?
  • Nilai Vlookup di Beberapa Lembar Kerja
  • Di excel, kita dapat dengan mudah menerapkan fungsi vlookup untuk mengembalikan nilai yang cocok dalam satu tabel di lembar kerja. Tapi, pernahkah Anda mempertimbangkan cara 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.

  • Bilah Formula Super (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
Urutkan komentar berdasarkan
komentar (8)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Terima kasih banyak, Anda menghemat banyak pekerjaan di sini!





Mengenai rumus Anda {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

Saya berubah sedikit : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Salam,



Jeff
Komentar ini diminimalkan oleh moderator di situs
Bagaimana Anda bisa mengubah rumus sehingga Anda memiliki banyak keluaran di kolom D? Misalnya saya ingin mengisi kolom D dengan masing-masing negara, sehingga semua kota masing-masing akan dikembalikan di E? Satu-satunya cara yang dapat saya lihat adalah mengubah $D$2 secara manual
Komentar ini diminimalkan oleh moderator di situs
Setelah saya memperluas data, beberapa sel menunjukkan "0" bukannya kosong
Komentar ini diminimalkan oleh moderator di situs
Artinya ada data yang hilang atau tipe data yang salah di dalam sel dari kolom yang dirujuk. Misalnya, saya bekerja dengan banyak data dan saya ingin semua nomor ID untuk pekerja di bawah supervisor tertentu. Namun, di kolom tabel yang direferensikan dengan semua nomor ID pekerja, beberapa sel memiliki nama belakang pekerja di dalamnya dan bukan nomor ID mereka. Sel-sel persis tersebut menghasilkan 0 ketika seluruh rumus dimasukkan, dan ini karena itu adalah teks dalam kolom yang sebagian besar berupa angka.
Komentar ini diminimalkan oleh moderator di situs
bagaimana cara mengisi semua baris seperti di video tahun tanpa menekan ctrl+shift+enter
Komentar ini diminimalkan oleh moderator di situs
Tuan-tuan

maukah Anda berbagi dengan saya rumus tiga cara tetapi untuk data dalam tabel horizontal.
Komentar ini diminimalkan oleh moderator di situs
Halo, Ahmad,
Untuk mengatasi masalah Anda, silakan terapkan rumus di bawah ini:
Dapatkan hasilnya secara vertikal: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Dapatkan hasilnya secara horizontal: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge hasil dalam satu sel: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: Rumus ini adalah rumus array, Anda harus menekan Ctrl + Shift + Enter tombol bersama-sama untuk mendapatkan hasil yang benar.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Silakan dicoba, semoga ini bisa membantu Anda!
Komentar ini diminimalkan oleh moderator di situs
terima kasih atas dukungan salam dan respons cepat Anda
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL