Cara menggunakan fungsi Excel XLOOKUP -

Ringkasan

Fungsi Excel XLOOKUP adalah pengganti yang modern dan fleksibel untuk fungsi yang lebih lama seperti VLOOKUP, HLOOKUP, dan LOOKUP. XLOOKUP mendukung pencocokan perkiraan dan tepat, karakter pengganti (*?) Untuk pencocokan parsial, dan pencarian dalam rentang vertikal atau horizontal.

Tujuan

Nilai pencarian dalam rentang atau larik

Nilai kembali

Nilai yang cocok dari array kembali

Sintaksis

= XLOOKUP (pencarian, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Argumen

  • lookup - Nilai pencarian.
  • lookup_array - Larik atau rentang yang akan dicari.
  • return_array - Larik atau rentang untuk dikembalikan.
  • not_found - (opsional) Nilai yang akan dikembalikan jika tidak ditemukan kecocokan.
  • match_mode - (opsional) 0 = pencocokan tepat (default), -1 = pencocokan tepat atau terkecil berikutnya, 1 = pencocokan tepat atau lebih besar berikutnya, 2 = pencocokan karakter pengganti.
  • search_mode - (opsional) 1 = pencarian dari pertama (default), -1 = pencarian dari terakhir, 2 = pencarian biner menaik, -2 = pencarian biner menurun.

Versi: kapan

Excel 365

Catatan penggunaan

XLOOKUP adalah pengganti modern untuk fungsi VLOOKUP. Ini adalah fungsi fleksibel dan serbaguna yang dapat digunakan dalam berbagai situasi.

XLOOKUP dapat menemukan nilai dalam rentang vertikal atau horizontal, dapat melakukan perkiraan dan pencocokan tepat, dan mendukung karakter pengganti (*?) Untuk pencocokan parsial. Selain itu, XLOOKUP dapat mencari data mulai dari nilai pertama atau nilai terakhir (lihat detail jenis pencocokan dan mode pencarian di bawah). Dibandingkan dengan fungsi lama seperti VLOOKUP, HLOOKUP, dan LOOKUP, XLOOKUP menawarkan beberapa keunggulan utama.

Tidak ditemukan pesan

Saat XLOOKUP tidak dapat menemukan kecocokan, XLOOKUP mengembalikan kesalahan # N / A, seperti fungsi kecocokan lainnya di Excel. Tidak seperti fungsi pencocokan lainnya, XLOOKUP mendukung argumen opsional yang disebut not_found yang dapat digunakan untuk menimpa kesalahan # N / A ketika kesalahan itu akan muncul. Nilai umum untuk not_found mungkin "Tidak ditemukan", "Tidak ada yang cocok", "Tidak ada hasil", dll. Saat memberikan nilai untuk not_found, apit teks dengan tanda kutip ganda ("").

Catatan: Hati-hati jika Anda memberikan string kosong ("") untuk not_found. Jika tidak ada kecocokan yang ditemukan, XLOOKUP tidak akan menampilkan apa-apa selain # N / A. Jika Anda ingin melihat kesalahan # N / A saat kecocokan tidak ditemukan, hilangkan argumen sepenuhnya.

Jenis kecocokan

Secara default, XLOOKUP akan melakukan pencocokan tepat. Perilaku pencocokan dikontrol oleh argumen opsional yang disebut match_type, yang memiliki opsi berikut:

Jenis kecocokan Tingkah laku
0 (bawaan) Benar-benar cocok. Akan mengembalikan # N / A jika tidak ada pertandingan.
-1 Cocok persis atau item lebih kecil berikutnya.
1 Cocok persis atau item berikutnya yang lebih besar.
2 Pencocokan karakter pengganti (*,?, ~)

Mode pencarian

Secara default, XLOOKUP akan mulai mencocokkan dari nilai data pertama. Perilaku pencarian dikendalikan oleh argumen opsional yang disebut search_mode , yang menyediakan opsi berikut:

Mode pencarian Tingkah laku
1 (default) Cari dari nilai pertama
-1 Cari dari nilai terakhir (terbalik)
2 Nilai pencarian biner diurutkan dalam urutan menaik
-2 Nilai pencarian biner diurutkan dalam urutan menurun

Pencarian biner sangat cepat, tetapi data harus diurutkan sesuai kebutuhan. Jika data tidak diurutkan dengan benar, pencarian biner dapat memberikan hasil tidak valid yang terlihat normal.

Contoh # 1 - pencocokan tepat dasar

Secara default, XLOOKUP akan melakukan pencocokan tepat. Dalam contoh di bawah ini, XLOOKUP digunakan untuk mengambil Penjualan berdasarkan pencocokan tepat di Film. Rumus di H5 adalah:

=XLOOKUP(H4,B5:B9,E5:E9)

Penjelasan lebih detail ada di sini.

Contoh # 2 - kecocokan perkiraan dasar

Untuk mengaktifkan perkiraan kecocokan, berikan nilai untuk argumen "match_mode". Dalam contoh di bawah ini, XLOOKUP digunakan untuk menghitung diskon berdasarkan kuantitas, yang membutuhkan kecocokan perkiraan. Rumus di F5 menyediakan -1 untuk match_mode untuk mengaktifkan perkiraan kecocokan dengan perilaku "sama persis atau terkecil berikutnya":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Penjelasan lebih detail ada di sini.

Contoh # 3 - beberapa nilai

XLOOKUP dapat mengembalikan lebih dari satu nilai pada waktu yang sama untuk pertandingan yang sama. Contoh di bawah ini memperlihatkan bagaimana XLOOKUP dapat dikonfigurasi untuk mengembalikan tiga nilai yang cocok dengan satu rumus. Rumus di C5 adalah:

=XLOOKUP(B5,B8:B15,C8:E15)

Perhatikan kembali array (C8: E15) termasuk 3 kolom: First, Last, Department. Ketiga nilai dikembalikan dan masuk ke kisaran C5: E5.

Contoh # 4 - pencarian dua arah

XLOOKUP dapat digunakan untuk melakukan pencarian dua arah, dengan menumpuk satu XLOOKUP di dalam yang lain. Dalam contoh di bawah, XLOOKUP "dalam" mengambil seluruh baris (semua nilai untuk Glass), yang diserahkan ke XLOOKUP "luar" sebagai larik kembali. XLOOKUP luar menemukan grup yang sesuai (B) dan mengembalikan nilai yang sesuai (17.25) sebagai hasil akhir.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Lebih lengkapnya di sini.

Contoh # 5 - pesan tidak ditemukan

Seperti fungsi pencarian lainnya, jika XLOOKUP tidak menemukan nilai, ia mengembalikan kesalahan # N / A. Untuk menampilkan pesan khusus alih-alih # N / A, berikan nilai untuk argumen opsional "tidak ditemukan", diapit tanda kutip ganda (""). Misalnya, untuk menampilkan "Tidak ditemukan" ketika tidak ada film yang cocok ditemukan, berdasarkan lembar kerja di bawah ini, gunakan:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Anda dapat menyesuaikan pesan ini sesuka Anda: "Tidak ada yang cocok", "Film tidak ditemukan", dll.

Contoh # 6 - kriteria kompleks

Dengan kemampuan untuk menangani array secara native, XLOOKUP dapat digunakan dengan kriteria yang kompleks. Pada contoh di bawah, XLOOKUP mencocokkan catatan pertama di mana: akun dimulai dengan "x" dan wilayahnya adalah "timur" dan bulan bukan April:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Detail: (1) contoh sederhana, (2) contoh yang lebih kompleks.

Manfaat XLOOKUP

XLOOKUP menawarkan beberapa keunggulan penting, terutama dibandingkan dengan VLOOKUP:

  • XLOOKUP dapat mencari data di sebelah kanan atau kiri nilai pencarian
  • XLOOKUP dapat mengembalikan banyak hasil (contoh # 3 di atas)
  • XLOOKUP default ke pencocokan tepat (VLOOKUP default untuk perkiraan)
  • XLOOKUP dapat bekerja dengan data vertikal dan horizontal
  • XLOOKUP dapat melakukan pencarian terbalik (terakhir ke pertama)
  • XLOOKUP dapat mengembalikan seluruh baris atau kolom, bukan hanya satu nilai
  • XLOOKUP dapat bekerja dengan array secara native untuk menerapkan kriteria kompleks

Catatan

  1. XLOOKUP dapat bekerja dengan array vertikal dan horizontal.
  2. XLOOKUP akan mengembalikan # N / A jika nilai pencarian tidak ditemukan.
  3. The cari_deret harus memiliki dimensi yang kompatibel dengan return_array argumen, jika tidak XLOOKUP akan kembali # NILAI!
  4. Jika XLOOKUP digunakan di antara buku kerja, kedua buku kerja harus dibuka, jika tidak XLOOKUP akan mengembalikan #REF !.
  5. Seperti fungsi INDEX, XLOOKUP mengembalikan referensi sebagai hasilnya.

Video Terkait

Contoh dasar XLOOKUP Dalam video ini, kami akan menyiapkan fungsi XLOOKUP dengan contoh dasar. Mencocokkan nama Kota, kami akan mengambil Negara dan Populasi. Perkiraan kecocokan XLOOKUP Dasar Dalam video ini, kami akan menyiapkan fungsi XLOOKUP untuk melakukan perkiraan kecocokan untuk menghitung diskon berbasis kuantitas. XLOOKUP dengan logika boolean Dalam video ini kita akan melihat bagaimana menggunakan fungsi XLOOKUP dengan logika Boolean untuk menerapkan beberapa kriteria. XLOOKUP dengan beberapa nilai pencarian Dalam video ini, kami akan menyiapkan XLOOKUP untuk mengembalikan beberapa nilai dalam larik dinamis, dengan memberikan rentang nilai pencarian, bukan nilai pencarian tunggal.

Artikel yang menarik...