VLOOKUP adalah fungsi yang hebat. Tapi saya sering mendapat pertanyaan di salah satu seminar Power Excel saya dari seseorang yang ingin tahu apakah VLOOKUP dapat mengembalikan semua nilai yang cocok. Seperti yang Anda ketahui, VLOOKUP dengan False sebagai argumen keempat akan selalu mengembalikan kecocokan pertama yang ditemukannya. Pada tangkapan layar berikut, sel F2 mengembalikan 3623 karena ini adalah kecocokan pertama yang ditemukan untuk pekerjaan J1199.
Pertanyaannya, kemudian, dapatkah VLOOKUP mengembalikan semua pertandingan?
VLOOKUP tidak akan. Tapi fungsi lain bisa.
Jika Anda ingin menjumlahkan semua biaya dari pekerjaan J1199, Anda akan menggunakan =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Jika Anda memiliki nilai teks dan ingin menggabungkan semua hasil menjadi satu nilai, Anda dapat menggunakan =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Rumus ini hanya berfungsi di Office 365 dan Excel 2019.
Atau, Anda mungkin perlu mengembalikan semua hasil untuk satu pekerjaan ke dalam rentang lembar kerja yang baru. =FILTER(B2:C53,A2:A53=K1,"None Found")Fungsi baru yang akan hadir di Office 365 pada 2019 akan menyelesaikan masalah:
Terkadang, orang ingin melakukan semua VLOOKUP dan menjumlahkannya. Jika tabel pencarian Anda diurutkan, Anda bisa menggunakan =SUM(LOOKUP(B2:B53,M3:N5)).
Jika Anda perlu menjumlahkan semua VLOOKUP dengan versi Pencocokan Persis dari VLOOKUP, Anda harus memiliki akses ke Array Dinamis untuk menggunakan =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Untuk mempelajari selengkapnya tentang Dynamic Arrays, lihat Excel Dynamic Arrays Straight To The Point.
Menonton video
Transkrip Video
Belajar Excel Dari, Podcast Episode 2247: Bisakah Anda Mengembalikan Semua Nilai VLookUp?
Hei. Selamat datang kembali di netcast. Saya Bill Jelen. Dua pertanyaan muncul dalam seminar saya di Appleton, Wisconsin minggu lalu - keduanya terkait. Mereka berkata, hei, bagaimana cara mengembalikan semua VLOOKUP, oke? Dalam hal ini, seperti J1199 memiliki banyak korek api dan mereka, Anda tahu, ingin mengembalikan semuanya, dan pertanyaan pertama saya setiap kali ada yang bertanya kepada saya, ini adalah, apa yang ingin Anda lakukan dengan korek api itu? Apakah itu angka yang ingin Anda tambahkan atau teks yang ingin Anda gabungkan? Dan itu lucu. Dua pertanyaan dalam seminar yang sama, satu orang ingin menjumlahkan dan yang lainnya ingin menggabungkan hasilnya.
Jadi mari kita lihat keduanya. Periksa di deskripsi YouTube untuk daftar isi di mana Anda dapat beralih ke yang lain jika Anda ingin melihat hasil teks.
Oke, jadi, pertama-tama, jika kita ingin menambahkan semuanya, kita tidak akan menggunakan VLOOKUP sama sekali. Kami akan menggunakan fungsi yang disebut SUMIF atau SUMIFS yang akan menjumlahkan semua yang cocok dengan item ini. Jadi, SUMIFS. Inilah nilai numerik yang ingin kita jumlahkan dan saya akan menekan F4 untuk menguncinya. Dengan cara itu, saat saya menyalin ini, itu akan terus menunjuk ke kisaran yang sama, dan kemudian kami ingin memeriksa dan melihat apakah nomor JOB di kolom A, sekali lagi F4 di sana, adalah = ke nilai di sebelah kiri kami - dalam hal ini E2 - dan saat kita menyalinnya, kita akan melihat TOTAL untuk setiap item. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Mari kita lakukan sedikit pengecekan di sini. J1199. Totalnya adalah 25365. Baiklah. Jadi, itu berhasil. Jika itu angka dan Anda ingin mendapatkan semua angka dan menambahkannya, alihkan ke SUMIF atau SUMIFS, tetapi jika itu teks, oke, sekarang, fungsi ini baru di Office 365 pada Februari 2017. Jadi, jika Anda memiliki Excel 2016 atau Excel 2013 atau Excel 2010 atau yang lebih lama, Anda tidak akan memiliki fungsi ini. Ini adalah fungsi yang disebut TEXTJOIN. TEXTJOIN. Ini adalah fungsi lain dari (Joe McDade - 01:50) yang baru saja menghadirkan kepada kita semua rumus array dinamis yang hebat di Ignite pada tahun 2018, dan Joe memastikan bahwa TEXTJOIN akan berfungsi dengan array, yang sangat hebat.
Jadi, pembatas di sini adalah, SPASI, abaikan KOSONG pasti. Kami ingin mengabaikan KOSONG di sini karena kami akan menghasilkan banyak kekosongan di bagian selanjutnya, pernyataan IF. JIKA item di atas A2, F4, adalah = ke nomor PEKERJAAN ini di sini, maka saya ingin item yang sesuai dari kolom C, F4, jika tidak, saya ingin "" seperti itu. Tutup pernyataan IF itu. Tutup TEXTJOIN. Apakah saya harus menekan CONTROL + SHIFT + ENTER? Tidak, saya tidak. Ini memberi saya semua produk yang cocok seperti itu, oke? Jadi, mengembalikan semua VLOOKUP, jika kita ingin menjumlahkannya, ya, jika kita ingin menggabungkannya, ya. (= TEXTJOIN (“,”, Benar, JIKA ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Baiklah, Sekarang, ada satu kemungkinan lain di sini ketika orang bertanya kepada saya apakah mereka dapat mengembalikan semua VLOOKUP. Mungkin menjadi masalah di mana kita ingin mencari setiap biaya ini di sini dan mencari tahu BIAYA PENANGANAN dan kemudian menjumlahkan semuanya. Seperti, saya tidak ingin menempatkan VLOOKUP di sini dan VLOOKUP di sini dan VLOOKUP di sini dan VLOOKUP di sini. Saya hanya ingin melakukannya sekaligus dan, dalam hal ini, kita akan menggunakan fungsi SUM dan kemudian fungsi LOOKUP lama. LOOKUP mengatakan bahwa kita akan mencari semua nilai ini di kolom B. Saya tidak memerlukan F4 di sini karena saya tidak menyalinnya di mana pun. ,. Inilah tabel pencarian kami. ), tutup SUM, dan ia keluar dan melakukan VLOOKUP masing-masing individu dan kemudian menjumlahkannya semua seperti itu. (= SUM (LOOKUP (B2: B53, K3: L5)))
Baiklah. Semua topik ini adalah buku saya LIV: The 54 Greatest Tips Of All Time. Klik i di pojok kanan atas untuk mempelajari lebih lanjut.
Jadi, pertanyaannya adalah bisakah Anda mengembalikan semua VLOOKUP? Yah, semacam itu, tetapi tidak benar-benar menggunakan VLOOKUP. Kami akan menggunakan SUMIF, TEXTJOIN, atau SUM atau LOOKUP untuk menyelesaikannya.
Baiklah. Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Anda tahu, baiklah, saya telah membicarakan tentang array dinamis ini selama seminggu. Saya ingin membuat satu video di mana saya tidak menyentuh array dinamis karena saya tahu banyak orang belum memilikinya, tapi di sinilah kita. Itu hasil. Anda tahu, ini tidak sesuai abjad. Ini akan jauh lebih baik jika kita bisa mengurutkannya, dan jika Anda kebetulan memiliki larik dinamis baru, Anda bisa mengirimkannya ke fungsi SORT, SORT seperti ini, dan tekan ENTER, dan sekarang hasilnya akan diurutkan seperti itu.
Anda tahu, bahkan rumus ini bisa menjadi lebih baik dengan array dinamis. Pencarian mengharuskan Anda menggunakan, TRUE. Bagaimana jika Anda ingin menggunakan, FALSE? Kita bisa mengubahnya menjadi VLOOKUP, mencari semua teks ini ke dalam tabel itu, 2,. Dalam kasus ini, saya akan menggunakan TRUE tetapi, dalam kasus lain, Anda mungkin menggunakan FALSE. CONTROL + SHIFT + ENTER. Tidak. Ini hanya akan berhasil, oke? (= SUM (VLOOKUP (B2: B53, K3: L5,2, Benar)))
Dynamic Array yang keluar pada awal 2019 akan menyelesaikan begitu banyak masalah.
Terima kasih telah nongkrong selama pengambilan di sini. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: can-you-return-all-vlookup-values.xlsx
Ketika seseorang bertanya "Bisakah VLOOKUP mengembalikan semua kecocokan, jawabannya adalah Tidak. Tapi, ada banyak fungsi lain yang pada dasarnya dapat melakukan hal yang sama.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Normalisasikan data Anda seperti Anda meminta orang lain menormalkan datanya untuk Anda"
Kevin Lehrbass
