Excel 2020: Dua Belas Manfaat XLOOKUP - Tip Excel

Fungsi XLOOKUP baru diluncurkan ke Office 365 mulai November 2019. Joe McDaid dari tim Excel merancang XLOOKUP untuk menyatukan orang-orang yang menggunakan VLOOKUP dan orang-orang yang menggunakan INDEX / MATCH. Bagian ini akan membahas 12 manfaat XLOOKUP:

  1. Pencocokan tepat adalah defaultnya.
  2. Argumen ketiga VLOOKUP berbasis integer sekarang menjadi referensi yang tepat.
  3. IFNA built-in untuk menangani nilai yang hilang.
  4. XLOOKUP tidak memiliki masalah untuk pergi ke kiri.
  5. Temukan kecocokan berikutnya-lebih kecil atau berikutnya-besar tanpa mengurutkan tabel.
  6. XLOOKUP dapat melakukan HLOOKUP.
  7. Temukan kecocokan terakhir dengan mencari dari bawah.
  8. Karakter pengganti "tidak aktif" secara default, tetapi Anda dapat mengaktifkannya kembali.
  9. Kembalikan semua 12 bulan dalam satu rumus.
  10. Dapat mengembalikan referensi sel jika XLOOKUP berada di sebelah titik dua seperti XLOOKUP (); XLOOKUP ()
  11. Dapat melakukan pertandingan dua arah seperti yang bisa dilakukan INDEX (, MATCH, MATCH).
  12. Dapat menjumlahkan semua pencarian dalam satu rumus seperti yang bisa dilakukan LOOKUP.

Berikut sintaksnya: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Manfaat XLOOKUP 1: Pencocokan Tepat secara Default

99% rumus VLOOKUP saya diakhiri dengan, FALSE atau, 0 untuk menunjukkan kecocokan persis. Jika Anda selalu menggunakan versi pencocokan tepat dari VLOOKUP, Anda dapat mulai membiarkan mode_cocok mati dari fungsi XLOOKUP Anda.

Pada gambar berikut, Anda mencari W25-6 dari sel A4. Anda ingin mencari item itu di L8: L35. Saat ditemukan, Anda menginginkan harga yang sesuai dari kolom N. Tidak perlu menentukan False sebagai match_mode karena XLOOKUP secara default menggunakan pencocokan tepat.

XLOOKUP nilai dalam A4. Lihat di L8: L35. Kembalikan harga yang sesuai dari N8: N35.

Manfaat XLOOKUP 2: Results_Array adalah referensi, bukan integer

Pikirkan tentang rumus VLOOKUP yang akan Anda gunakan sebelum XLOOKUP. Argumen ketiga akan menjadi 3 untuk menunjukkan bahwa Anda ingin mengembalikan kolom ke-3. Selalu ada bahaya bahwa rekan kerja yang tidak mengerti akan memasukkan (atau menghapus) kolom di tabel Anda. Dengan kolom tambahan di tabel, VLOOKUP yang mengembalikan harga akan mulai mengembalikan deskripsi. Karena XLOOKUP menunjuk ke referensi sel, rumus menulis ulang sendiri untuk tetap menunjuk ke harga yang sekarang ada di kolom O.

VLOOKUP lama akan gagal jika seseorang menyisipkan kolom baru di tabel pencarian. XLOOKUP terus bekerja.

XLOOKUP Manfaat 3: IFNA dibangun sebagai argumen opsional

Kesalahan # N / A yang ditakuti dikembalikan saat nilai pencarian Anda tidak ditemukan dalam tabel. Di masa lalu, untuk mengganti # N / A dengan yang lain, Anda harus menggunakan IFERROR atau IFNA yang melingkari VLOOKUP.

Ketika sebuah item tidak ditemukan, ia mengembalikan # N / A dari VLOOKUP atau XLOOKUP…

Berkat saran dari Rico di saluran YouTube saya, tim Excel memasukkan argumen opsional keempat untuk if_not_found. Jika Anda ingin mengganti kesalahan # N / A dengan nol, cukup tambahkan, 0 sebagai argumen keempat. Atau, Anda dapat menggunakan beberapa teks, seperti "Nilai tidak ditemukan".

Argumen opsional keempat di XLOOKUP adalah "jika tidak ditemukan". Letakkan 0 atau "Tidak ditemukan" di sana.

XLOOKUP Manfaat 4: Tidak ada masalah melihat ke kiri bidang kunci

VLOOKUP tidak dapat melihat ke kiri bidang kunci tanpa menggunakan VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Dengan XLOOKUP, tidak ada masalah memiliki Results_array di sebelah kiri Lookup_array.

Dengan XLOOKUP, tidak ada masalah mengembalikan Kategori dari kolom F sambil mencari nomor bagian di kolom G. Ini selalu menjadi kelemahan VLOOKUP: tidak dapat melihat ke kiri.

Manfaat XLOOKUP 5: Kecocokan berikutnya-lebih kecil atau berikutnya-lebih besar tanpa penyortiran

VLOOKUP memiliki opsi untuk mencari kecocokan tepat atau hanya nilai yang lebih kecil. Anda bisa membiarkan argumen keempat keluar dari VLOOKUP, atau mengubah False menjadi True. Agar ini berfungsi, tabel pemeta harus diurutkan dalam urutan menaik.

Contoh versi Approximate Match dari VLOOKUP. Setiap penjualan dari 10 ribu hingga 20 ribu mendapat bonus $ 12.

Tetapi VLOOKUP tidak memiliki kemampuan untuk mengembalikan kecocokan persis atau item berikutnya yang lebih besar. Untuk itu, Anda harus beralih menggunakan MATCH dengan -1 sebagai match_mode dan Anda harus berhati-hati karena tabel pencarian diurutkan menurun.

Argumen opsional kelima XLOOKUP match_mode hanya dapat mencari pencocokan tepat, sama dengan atau hanya lebih kecil, sama dengan atau hanya lebih besar. Perhatikan bahwa nilai di XLOOKUP lebih masuk akal daripada di MATCH:

  • -1 menemukan nilainya sama dengan atau lebih kecil
  • 0 temukan yang sama persis
  • 1 menemukan nilai sama dengan atau hanya lebih besar.

Namun, bagian yang paling menakjubkan: tabel pencarian tidak harus diurutkan dan match_mode apa pun akan berfungsi.

Di bawah, match_mode -1 temukan item berikutnya yang lebih kecil.

Argumen kelima XLOOKUP adalah Match_Mode. 0 untuk pencocokan tepat. Satu negatif digunakan untuk Pencocokan Tepat atau Item Lebih Kecil Berikutnya. Positif 1 untuk pencocokan tepat atau item berikutnya yang lebih besar. 2 untuk Pertandingan Wildcard. Untuk mencerminkan apa yang akan dilakukan VLOOKUP dengan True dalam argumen keempat, tempatkan yang negatif sebagai argumen match_mode di XLOOKUP.

Di sini, match_mode 1, menemukan kendaraan apa yang dibutuhkan tergantung pada jumlah orang di party. Perhatikan bahwa tabel pencarian tidak diurutkan berdasarkan penumpang dan nama kendaraan ada di sebelah kiri tombol.

XLOOKUP dapat melakukan sesuatu yang tidak dapat dilakukan oleh VLOOKUP: temukan yang sama persis atau lebih besar. Dalam hal ini, perusahaan tur memiliki daftar reservasi. Berdasarkan jumlah penumpang, tabel lookup menunjukkan kendaraan apa yang Anda butuhkan untuk orang-orang tersebut.

Tabel tersebut mengatakan:

  • Bus menampung 64 orang
  • Mobil menampung 4 orang
  • Motorcyle menampung 1 orang
  • Tour Van menampung 12 orang
  • Van menampung 6 orang.

Sebagai bonus, data diurutkan berdasarkan Kendaraan (dalam solusi lama, menggunakan MATCH, tabel harus diurutkan secara menurun menurut Kapasitas. Juga: Kendaraan di sebelah kiri Kapasitas.

XLOOKUP Manfaat 6: Ke samping XLOOKUP menggantikan HLOOKUP

Lookup_array dan results_array bisa dibuat horizontal dengan XLOOKUP, sehingga mudah untuk mengganti HLOOKUP.

Di sini tabel pemeta berbentuk horizontal. Di masa lalu, ini akan membutuhkan HLOOKUP, tetapi XLOOKUP dapat menangani tabel yang miring.

Manfaat XLOOKUP 7: Cari kecocokan terbaru dari bawah

Saya memiliki video lama di YouTube yang menjawab pertanyaan dari peternakan kuda Inggris. Mereka memiliki armada kendaraan. Setiap kali sebuah kendaraan datang untuk bahan bakar atau servis, mereka mencatat kendaraan, tanggal, dan jarak tempuh dalam spreadsheet. Mereka ingin menemukan jarak tempuh terbaru yang diketahui untuk setiap kendaraan. Sementara MAXIFS era Excel-2017 mungkin memecahkan masalah ini hari ini, solusi bertahun-tahun yang lalu adalah rumus misterius menggunakan LOOKUP dan melibatkan pembagian dengan nol.

Hari ini, argumen keenam opsional XLOOKUP memungkinkan Anda menentukan bahwa pencarian harus dimulai dari bagian bawah kumpulan data.

Temukan kecocokan terakhir dalam daftar.

Catatan

Meskipun ini adalah peningkatan besar, ini hanya memungkinkan Anda menemukan pertandingan pertama atau terakhir. Beberapa orang berharap ini akan membiarkan Anda menemukan kecocokan kedua atau ketiga, tetapi itu bukan maksud dari argumen search_mode.

Peringatan

Gambar di atas menunjukkan bahwa ada mode pencarian yang menggunakan pencarian biner lama. Joe McDaid menyarankan agar tidak menggunakan ini. Pertama, algoritme pencarian yang ditingkatkan dari 2018 cukup cepat sehingga tidak ada manfaat kecepatan yang signifikan. Kedua, Anda menghadapi risiko rekan kerja yang tidak mengerti menyortir tabel pencarian dan memberikan jawaban yang salah.

XLOOKUP Manfaat 8: Karakter pengganti "dinonaktifkan" secara default

Kebanyakan orang tidak menyadari bahwa VLOOKUP memperlakukan tanda bintang, tanda tanya, dan tilde sebagai karakter wildcard seperti yang dijelaskan di "# 51 Gunakan Wildcard di VLOOKUP" di halaman 143. Dengan XLOOKUP, karakter pengganti dimatikan secara default. Jika Anda ingin XLOOKUP memperlakukan karakter ini sebagai wildcard, gunakan 2 sebagai Match_Mode.

Sangat sedikit orang yang menyadari bahwa VLOOKUP memperlakukan tanda bintang dalam nilai pencarian sebagai karakter pengganti. Secara default, XLOOKUP tidak menggunakan karakter pengganti, tetapi Anda dapat memaksanya untuk berperilaku seperti VLOOKUP jika Anda menggunakan Mode Pencocokan 2: Pencocokan Karakter Karakter Pengganti.

Manfaat XLOOKUP 9: Kembalikan Semua 12 Bulan dalam Satu Formula!

Ini benar-benar keuntungan dari Dynamic Arrays, tetapi ini adalah alasan favorit saya untuk menyukai XLOOKUP. Jika Anda harus mengembalikan semua 12 bulan dalam pencarian, satu rumus yang dimasukkan dalam B6 dengan return_array persegi akan memberikan banyak hasil. Hasil tersebut akan menyebar ke sel yang berdekatan.

Pada gambar di bawah ini, satu rumus yang dimasukkan di B7 mengembalikan semua 12 jawaban yang ditampilkan di B7: M7.

XLOOKUP tunggal di kolom Januari mengembalikan angka untuk Januari hingga Desember. Ini dilakukan dengan menentukan result_array dengan 12 kolom.

XLOOKUP Manfaat 10: Dapat mengembalikan Referensi Sel Jika Berdekatan dengan Usus Besar

Yang ini rumit tapi indah. Di masa lalu, ada tujuh fungsi yang akan berubah dari mengembalikan nilai sel menjadi mengembalikan referensi sel jika fungsi tersebut menyentuh titik dua. Sebagai contoh, lihat Gunakan A2: INDEX () sebagai OFFSET Non-Volatile. XLOOKUP adalah fungsi delapan untuk menawarkan perilaku ini, bergabung dengan CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET, dan SWITCH.

Perhatikan gambar berikut. Seseorang pilih Cherry di E4 dan Fig di E5. Anda menginginkan rumus yang akan menjumlahkan semuanya dari B6 hingga B9.

Gambar tersebut memperlihatkan dua rumus XLOOKUP dalam dua sel. Yang pertama mengembalikan 15 dari sel B6. Kedua retuns 30 dari B9. Tapi lalu di sel ketiga, ada rumus yang menggabungkan dua rumus XLOOKUP dengan titik dua lalu membungkusnya dalam fungsi SUM. Hasilnya adalah SUM dari B6: B9, karena XLOOKUP bisa mengembalikan referensi sel jika fungsinya muncul di sebelah operator seperti titik dua. Untuk membuktikan bahwa ini berhasil, beberapa gambar berikutnya akan menunjukkan rumus ini dalam dialog Formula Evaluasi.

Pada gambar di atas, Anda dapat melihat bahwa XLOOKUP dari E4 akan mengembalikan 15 dari sel B6. XLOOKUP E5 akan mengembalikan 30 dari B9. Namun, jika Anda mengambil dua fungsi XLOOKUP dari sel D9 dan D10 dan menggabungkannya dengan titik dua di antaranya, perilaku XLOOKUP berubah. Alih-alih mengembalikan 15, XLOOKUP pertama mengembalikan alamat sel B6!

Untuk membuktikan ini, saya telah memilih D7 dan menggunakan Rumus, Rumus Evaluasi. Setelah menekan Evaluasi dua kali, bagian selanjutnya yang akan dihitung adalah XLOOKUP ("Cherry", A4: A29, B4: B29), seperti yang ditunjukkan di sini.

Ini memperlihatkan dialog Evaluasi Rumus sebelum mengevaluasi XLOOKUP pertama. XLOOKUP ini muncul tepat sebelum titik dua.

Tekan Evaluasi lagi dan luar biasa, rumus XLOOKUP mengembalikan $ B $ 6 alih-alih 15 yang disimpan di B6. Ini terjadi karena ada titik dua segera setelah rumus XLOOKUP ini.

Klik Evaluasi dan XLOOKUP pertama mengembalikan $ B $ 6, bukan 15.

Tekan Evaluasi dua kali lagi, dan rumus sementara akan menjadi = SUM (B6: B9).

Setelah mengevaluasi XLOOKUP kedua, rumus interimnya adalah = SUM (B6: B9).

Ini adalah perilaku luar biasa yang tidak diketahui kebanyakan orang. MVP Excel Charles Williams memberi tahu saya bahwa ini dapat dipicu dengan salah satu dari tiga operator berikut di samping XLOOKUP:

  • Usus besar
  • Spasi (Operator persimpangan)
  • Koma (Operator gabungan)

Manfaat XLOOKUP 11: Pertandingan dua arah seperti INDEX (, MATCH, MATCH)

Untuk semua teman VLOOKUP saya, orang-orang INDEX / MATCH telah menunggu untuk melihat apakah XLOOKUP dapat menangani pertandingan dua arah. Kabar baiknya: itu bisa melakukannya. Kabar buruknya: metodologi ini sedikit berbeda dari yang diharapkan oleh para penggemar INDEX / MATCH. Mungkin sedikit berlebihan. Tapi saya yakin mereka bisa sampai pada metode ini.

Untuk pertandingan dua arah, Anda ingin mencari baris mana yang berisi nomor rekening A621 yang ditampilkan di J3. Jadi, XLOOKUP dimulai dengan cukup mudah: = XLOOKUP (J3, A5: A15. Tetapi kemudian Anda harus memberikan result_array. Anda dapat menggunakan trik yang sama seperti di XLOOKUP Manfaat 9: Mengembalikan Semua 12 Bulan dalam Formula Tunggal di atas, tetapi menggunakannya untuk mengembalikan vektor vertikal. XLOOKUP bagian dalam mencari bulan J4 di judul bulan di B4: G4. Return_array ditentukan sebagai B5: G15. Hasilnya adalah XLOOKUP bagian dalam mengembalikan array seperti yang ditunjukkan di I10 : I20 di bawah. Karena A621 ditemukan di sel kelima dari lookup_array dan 104 ditemukan di sel kelima dari results_array, Anda mendapatkan jawaban yang benar dari rumus. Di bawah, J6 menunjukkan cara lama. J7 mengembalikan cara baru.

XLookup J3 dalam daftar akun di A5: A15. Untuk Hasil Array, gunakan XLOOKUP (J4, B4: G4, B5: G15). Dalam rumus ini, B4: G4 adalah daftar bulan. B5: G15 adalah larik nilai persegi panjang untuk semua akun untuk semua bulan. Di sel lain, hanya XLOOKUP bagian dalam yang memperlihatkan cara mengembalikan seluruh kolom nilai untuk Mei.

Manfaat XLOOKUP 12: Menjumlahkan semua nilai pencarian dalam satu rumus

Fungsi LOOKUP kuno menawarkan dua trik aneh. Pertama, jika Anda mencoba mencari tahu jumlah total biaya bonus yang harus dikumpulkan, Anda dapat meminta LOOKUP untuk mencari semua nilai dalam satu rumus. Pada gambar di bawah, LOOKUP (C4: C14 melakukan 11 pencarian. Tetapi fungsi LOOKUP tidak menawarkan pencocokan tepat dan membutuhkan tabel pencarian untuk diurutkan.

Cari 13 nilai dan jumlahkan. Ini sebelumnya berfungsi dengan LOOKUP, tetapi juga berfungsi dengan XLOOKUP. Tentukan semua nilai pencarian C4: C14 sebagai argumen pertama. Bungkus XLOOKUP dalam fungsi SUM.

Dengan XLOOKUP, Anda dapat menentukan rentang sebagai lookup_value dan XLOOKUP akan mengembalikan semua jawaban. Manfaatnya adalah XLOOKUP dapat melakukan pencocokan persis.

Trik menggunakan LOOKUP untuk menjumlahkan semua hasil pencarian hanya bekerja dengan versi pencarian yang mendekati. Di sini, XLOOKUP melakukan pencocokan tepat pada semua nama di L4: L14 dan mendapatkan total semua hasil.

Tip Bonus: Bagaimana dengan Twisted LOOKUP?

MVP Excel Mike Girvin sering menunjukkan trik fungsi LOOKUP di mana Lookup_Vector vertikal dan Result_Vector horizontal. XLOOKUP tidak akan mendukung trik ini secara native. Namun, jika Anda sedikit curang dan membungkus result_array dalam fungsi TRANSPOSE, Anda dapat mengelola pencarian yang bengkok.

Di sini array pencarian adalah vertikal dan array hasil horizontal. Fungsi LOOKUP lama dapat menangani ini ,, tetapi untuk melakukannya dengan XLOOKUP, Anda harus membungkus salah satu array dalam TRANSPOSE.

Artikel yang menarik...