Loncat ke daftar isi utama

Vlookup dan kembalikan beberapa nilai berdasarkan satu atau beberapa kriteria

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($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Note: Dalam rumus di atas, C2:C20 adalah kolom berisi rekaman yang cocok yang ingin Anda kembalikan; A2: A20 adalah kolom berisi kriteria; dan E2 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:

Tip:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan nilai yang lebih spesifik secara vertikal, harap terapkan rumus di bawah ini, dan tekan Ctrl + Shift + Enter kunci.

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


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($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Note: Dalam rumus di atas, C2: C20 adalah kolom berisi rekaman yang cocok yang ingin Anda kembalikan; A2: A20 adalah kolom berisi kriteria; dan F1 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:

Tip:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan nilai yang lebih spesifik secara horizontal, silakan terapkan rumus di bawah ini, dan tekan Ctrl + Shift + Enter kunci.

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


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$20=F1,$C$2:$C$20,""))

Note: Dalam rumus di atas, C2: C20 adalah kolom berisi rekaman yang cocok yang ingin Anda kembalikan; A2: A20 adalah kolom berisi kriteria; dan F1 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:

Untuk Vlookup dan mengembalikan semua nilai yang cocok berdasarkan nilai yang lebih spesifik dalam satu sel, silakan terapkan rumus di bawah ini, dan tekan Ctrl + Shift + Enter kunci.

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

Catatan: Rumus ini hanya berhasil diterapkan di Excel 2016 dan versi yang lebih baru. Jika Anda tidak memiliki Excel 2016, silakan lihat di sini 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
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =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))),"" )
Get the results horizontally: =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 the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

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

Pease try, hope this can help you!
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations