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

Cara Menggunakan Fungsi XLOOKUP BARU & LANJUTAN di Excel (10 Contoh)

Excel yang baru XLOOKUP adalah fungsi Pencarian paling kuat dan termudah yang dapat ditawarkan Excel. Melalui upaya yang tak henti-hentinya, Microsoft akhirnya merilis fungsi XLOOKUP ini untuk menggantikan VLOOKUP, HLOOKUP, INDEX+MATCH, dan fungsi lookup lainnya.

Dalam tutorial ini, kami akan menunjukkan kepada Anda apa kelebihan XLOOKUP serta bagaimana Anda bisa mendapatkannya dan menerapkannya? untuk memecahkan masalah pencarian yang berbeda.

Bagaimana Mendapatkan XLOOKUP?

Sintaks Fungsi XLOOKUP

Contoh Fungsi XLOOKUP

Unduh File Contoh XLOOKUP

Bagaimana Mendapatkan XLOOKUP?

Sejak Fungsi XLOOKUP is hanya tersedia in Excel untuk Microsoft 365, Excel 2021, dan Excel untuk web, Anda dapat memutakhirkan Excel ke versi yang tersedia untuk mendapatkan XLOOKUP.

Sintaks Fungsi XLOOKUP

Fungsi XLOOKUP mencari rentang atau larik dan kemudian mengembalikan nilai dari hasil pencocokan pertama. itu Sintaksis dari fungsi XLOOKUP adalah sebagai berikut:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

fungsi xlookup 1

Argumen:

  1. Nilai_pencarian (wajib): nilai yang Anda cari. Itu bisa di kolom mana saja dari rentang table_array.
  2. Lookup_array (wajib): larik atau rentang tempat Anda mencari nilai pencarian.
  3. Return_array (wajib): larik atau rentang dari tempat Anda ingin mendapatkan nilainya.
  4. If_not_found (opsional): nilai yang akan dikembalikan ketika kecocokan yang valid tidak ditemukan. Anda dapat menyesuaikan teks di [if_not_found] untuk menunjukkan bahwa tidak ada yang cocok.
    Jika tidak, nilai yang dikembalikan akan menjadi #N/A secara default.
  5. Mode_cocok (opsional): di sini Anda dapat menentukan cara mencocokkan lookup_value dengan nilai di lookup_array.
    • 0 (default) = Sama persis. Jika tidak ada yang cocok, kembalikan #N/A.
    • -1 = Sama persis. Jika tidak ada kecocokan yang ditemukan, kembalikan nilai yang lebih kecil berikutnya.
    • 1 = Sama persis. Jika tidak ada kecocokan yang ditemukan, kembalikan nilai yang lebih besar berikutnya.
    • 2 = Kecocokan sebagian. Gunakan karakter wildcard seperti *, ? dan ~ untuk menjalankan pertandingan wildcard.
  6. Search_mode (opsional): di sini Anda dapat menentukan urutan pencarian yang akan dilakukan.
    • 1 (default) = Cari lookup_value dari item pertama hingga item terakhir di lookup_array.
    • -1 = Cari lookup_value dari item terakhir ke item pertama. Ini membantu ketika Anda perlu mendapatkan hasil pencocokan terakhir di lookup_array.
    • 2 = Melakukan pencarian biner yang membutuhkan lookup_array diurutkan dalam urutan menaik. Jika tidak diurutkan, hasil pengembalian akan menjadi tidak valid.
    • -2 = Melakukan pencarian biner yang membutuhkan lookup_array diurutkan dalam urutan menurun. Jika tidak diurutkan, hasil pengembalian akan menjadi tidak valid.

Untuk informasi terperinci tentang argumen fungsi XLOOKUP, lakukan hal berikut:

1. Ketik di bawah sintaks ke dalam sel kosong, harap dicatat Anda hanya perlu mengetikkan satu sisi braket.

=XLOOKUP(

fungsi xlookup 2

2. tekan Ctrl + A, kemudian kotak prompt muncul yang menunjukkan argumen fungsi. Dan sisi lain dari braket selesai secara otomatis.

fungsi xlookup 3

3. Tarik ke bawah panel data, maka Anda dapat melihat semua enam argumen fungsi dari XLOOKUP.

fungsi xlookup 4 >>> fungsi xlookup 5

Contoh Fungsi XLOOKUP

Saya yakin Anda telah menguasai prinsip dasar fungsi XLOOKUP sekarang. Mari selami contoh-contoh praktis dari XLOOKUP.

Contoh 1: Pencocokan tepat

Lakukan pencocokan tepat dengan XLOOKUP

Apakah Anda pernah merasa frustrasi karena Anda harus menentukan mode pencocokan tepat setiap kali Anda menggunakan VLOOKUP? Untungnya, masalah ini tidak ada lagi saat Anda mencoba fungsi XLOOKUP yang luar biasa. Secara default, XLOOKUP menghasilkan kecocokan yang tepat.

Sekarang, misalkan Anda memiliki daftar inventaris perlengkapan kantor, dan Anda perlu mengetahui harga satuan satu item, katakanlah mouse, lakukan hal berikut.

fungsi xlookup 6

ketik di bawah rumus ke dalam sel kosong F2, dan tekan tombol Enter kunci untuk mendapatkan hasil.

=XLOOKUP(E2,A2:A10,C2:C10)

fungsi xlookup 7

Sekarang Anda tahu harga satuan Mouse dengan formula XLOOKUP lanjutan. Karena kode pencocokan telah ditetapkan secara default ke pencocokan tepat, Anda tidak perlu menentukannya. Jauh lebih mudah dan efisien daripada VLOOKUP.

Hanya Beberapa Klik untuk Mendapatkan Kecocokan yang Tepat

Mungkin Anda menggunakan versi Excel yang lebih rendah dan belum memiliki rencana untuk meningkatkan ke Excel 2021 atau Microsoft 365. Dalam hal ini, saya akan merekomendasikan fitur yang berguna - Cari Nilai dalam rumus Daftar of Kutools untuk Excel. Dengan fitur ini, Anda bisa mendapatkan hasil tanpa rumus rumit atau akses ke XLOOKUP.

Dengan our Add-in Excel instsemua, silakan lakukan sebagai berikut:

1. Klik sel untuk meletakkan hasil yang cocok.

2. Pergi ke Kutools tab, klik Pembantu Formula, lalu klik Pembantu Formula dalam daftar tarik-turun.

fungsi xlookup 8

3. Dalam Kotak dialog Pembantu Rumus, silakan konfigurasikan sebagai berikut:

  • Pilih Lookup dalam Bagian Jenis Rumus;
  • Dalam majalah Pilih bagian rumus, pilih Cari nilai dalam daftar;
  • Dalam majalah Bagian masukan argumen, lakukan hal berikut:
    • Dalam majalah Kotak tabel_array, pilih rentang data yang berisi nilai pencarian dan nilai hasil;
    • Dalam majalah kotak pencarian_nilai, pilih sel atau rentang nilai yang Anda cari. Harap dicatat itu harus di kolom pertama dari table_array;
    • Dalam majalah kotak kolom, pilih kolom tempat Anda akan mengembalikan nilai yang cocok.

fungsi xlookup 9

4. Klik OK tombol untuk mendapatkan hasilnya.

fungsi xlookup 10

Klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari.


Contoh 2. Perkiraan Pertandingan

Lakukan perkiraan kecocokan dengan XLOOKUP

Untuk menjalankan perkiraan pencarian, Anda perlu atur mode kecocokan ke 1 atau -1 dalam argumen kelima. Ketika tidak ada kecocokan persis yang ditemukan, ia mengembalikan nilai yang lebih besar atau lebih kecil berikutnya.

Dalam hal ini, Anda perlu mengetahui tarif pajak penghasilan staf Anda. Di sisi kiri spreadsheet adalah Kurung Pajak Penghasilan Federal untuk tahun 2021. Bagaimana Anda bisa mendapatkan tarif pajak staf Anda di kolom E? Jangan khawatir. Silakan lakukan sebagai berikut:

1. Ketik di bawah rumus ke dalam sel kosong E2, dan tekan tombol Enter kunci untuk mendapatkan hasil.
Kemudian ubah format hasil yang dikembalikan sesuai kebutuhan.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

fungsi xlookup 11 >>> fungsi xlookup 12

Catatan: Argumen keempat [If_not_found] adalah opsional, jadi saya abaikan saja.

2. Sekarang Anda tahu tarif pajak sel D2. Untuk mendapatkan sisa hasil, Anda perlu konversi referensi sel dari lookup_array dan return_array menjadi absolute.

  • Klik dua kali sel E2 untuk menampilkan rumus =XLOOKUP(D2,B2:B8,A2:A8,,1);
  • Pilih rentang pencarian B2:B8 dalam rumus, tekan tombol F4 untuk mendapatkan $B$2:$B$8;
  • Pilih rentang pengembalian A2:A8 dalam rumus, tekan tombol F4 untuk mendapatkan $A$2:$A$8;
  • Tekan Enter tombol untuk mendapatkan hasil sel E2.
fungsi xlookup 13 >>> fungsi xlookup 14

3. Kemudian seret gagang isian ke bawah untuk mendapatkan semua hasil.

fungsi xlookup 15

Catatan:

  • Menekan tombol F4 pada keyboard memungkinkan Anda untuk ubah referensi sel menjadi referensi absolut dengan menambahkan tanda dolar sebelum baris dan kolom.
  • Setelah menerapkan referensi absolut untuk mencari dan mengembalikan jangkauan, kami mengubah rumus di sel E2 ke versi ini:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

  • Bila Anda seret gagang isian ke bawah dari sel E2, rumusnya di setiap sel kolom E adalah berubah hanya dalam aspek lookup_value.
    Misalnya, rumus di E13 sekarang diubah menjadi ini:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

Contoh 3: Pertandingan Wildcard

Lakukan pertandingan Wildcard dengan XLOOKUP

Sebelum kita melihat ke dalam Fungsi pencocokan wildcard XLOOKUP, mari kita lihat dulu apa itu wildcard?.

Di Microsoft Excel, wildcard adalah jenis karakter khusus yang dapat menggantikan karakter apa pun. Hal ini terutama bermanfaat ketika Anda ingin melakukan pencarian kecocokan sebagian.

Ada tiga jenis wildcard: tanda bintang (*), tanda tanya (?), dan pasang surut (~).

  • Asterisk (*) mewakili sejumlah karakter dalam teks;
  • Tanda tanya (?) singkatan dari setiap karakter tunggal dalam teks;
  • Tilde (~) digunakan untuk mengubah wildcard (*, ? ~) menjadi karakter literal. Tempatkan tilde (~) di depan wildcard untuk memenuhi fungsi ini;

Dalam kebanyakan kasus, ketika kami menjalankan fungsi pencocokan wildcard XLOOKUP, kami menggunakan karakter asterisk (*). Sekarang mari kita lihat cara kerja kecocokan wildcard.

Misalkan Anda memiliki daftar Kapitalisasi Pasar Saham dari 50 Perusahaan Amerika Terbesar, dan Anda ingin mengetahui kapitalisasi pasar dari beberapa perusahaan tetapi singkatnya nama perusahaan, ini adalah skenario sempurna untuk pertandingan wildcard. Silakan ikuti saya langkah demi langkah untuk melakukan triknya.

fungsi xlookup 16

Catatan: Untuk melakukan kecocokan wildcard, yang paling penting adalah mengatur argumen kelima [mode_pertandingan] ke 2.

1. Ketik di bawah rumus ke sel kosong H3, dan tekan tombol Enter kunci untuk mendapatkan hasil.

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

fungsi xlookup 17 >>> fungsi xlookup 18

2. Sekarang Anda tahu hasil dari sel H3. Untuk mendapatkan sisa hasil, Anda perlu perbaiki lookup_array dan return_array dengan menempatkan kursor dalam array dan menekan tombol F4. Maka rumus pada H3 menjadi:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. Seret gagang isian ke bawah untuk mendapatkan semua hasil.

fungsi xlookup 19

Catatan:

  • Nilai_pencarian rumus di sel H3 adalah "*"&G3&"*". Kami gabungkan wildcard asterisk (*) dengan nilai G3 menggunakan simbol untuk 'dan (&).
  • Argumen keempat [If_not_found] adalah opsional, jadi saya hilangkan saja.
Contoh 4: Lihat ke kiri

Lihat ke kiri menggunakan XLOOKUP

Satu kerugian dari VLOOKUP apakah itu benar dibatasi untuk melakukan pencarian di sebelah kanan kolom pencarian. Jika Anda mencoba mencari nilai yang tersisa di kolom pencarian, Anda akan mendapatkan kesalahan #N/A. Jangan khawatir. XLOOKUP adalah fungsi pencarian yang sempurna untuk memecahkan masalah ini.

XLOOKUP dirancang untuk mencari nilai ke kiri atau kanan dari kolom pencarian. Ini tidak memiliki batas dan memenuhi kebutuhan pengguna Excel. Pada contoh di bawah ini, kami akan menunjukkan triknya.

Misalkan Anda memiliki daftar negara dengan kode telepon, dan Anda ingin mencari nama negara dengan kode telepon yang dikenal.

fungsi xlookup 20

Kita perlu mencari kolom C dan mengembalikan nilai di kolom A. Lakukan hal berikut:

1. Ketik di bawah rumus ke dalam sel kosong G2.

=XLOOKUP(F2,C2:C11,A2:A11)

2. tekan Enter kunci untuk mendapatkan hasil.

fungsi xlookup 21

Catatan: Fungsi XLOOKUP melihat ke kiri dapat menggantikan Indeks dan Mencocokkan untuk mencari nilai ke kiri.

Nilai pencarian dari kanan ke kiri dengan beberapa klik

Bagi yang tidak ingin mengingat rumus, di sini saya akan merekomendasikan fitur yang berguna - Cari dari Kanan ke Kiri of Kutools untuk Excel. Dengan fitur ini, Anda dapat melakukan pencarian dari kanan ke kiri dalam beberapa detik.

Dengan our Add-in Excel instsemua, silakan lakukan sebagai berikut:

1. Pergi ke Kutools tab di Excel, temukan PENCARIAN Super, Dan klik LOOKUP dari Kanan ke Kiri di daftar drop-down.

fungsi xlookup 22

2. Dalam LOOKUP dari kotak dialog Kanan ke Kiri, Anda perlu mengonfigurasi sebagai berikut:

  • Dalam majalah Nilai pencarian dan bagian rentang Output, sebutkan rentang pencarian serta jangkauan keluaran;
  • Dalam majalah Bagian rentang data, memasukkan jangkauan data, lalu tentukan kolom kunci serta kolom kembali;

fungsi xlookup 23

3. Klik OK tombol untuk mendapatkan hasilnya.

fungsi xlookup 24

Klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari.


Contoh 5: Pencarian Vertikal atau Horizontal

Lakukan pencarian Vertikal atau Horizontal dengan XLOOKUP

Sebagai pengguna Excel, Anda mungkin sudah familiar dengan fungsi VLOOKUP dan HLOOKUP. VLOOKUP adalah untuk melihat secara vertikal di kolom serta HLOOKUP adalah melihat secara horizontal berturut-turut.

Sekarang XLOOKUP baru menggabungkan keduanya, yang berarti bahwa Anda hanya perlu menggunakan satu sintaks untuk melakukan pencarian vertikal atau pencarian horizontal. Jenius, bukan?

Pada contoh di bawah ini, kami akan mengilustrasikan bagaimana Anda hanya menggunakan satu sintaks XLOOKUP untuk menjalankan pencarian secara vertikal atau horizontal.

Untuk melakukan pencarian vertikal, ketik di bawah rumus di sel kosong E2, tekan tombol Enter kunci untuk mendapatkan hasil.

=XLOOKUP(E1,A2:A13,B2:B13)

fungsi xlookup 25

Untuk melakukan pencarian horizontal, ketik di bawah rumus di sel kosong P2, tekan tombol Enter kunci untuk mendapatkan hasil.

=XLOOKUP(P1,B1:M1,B2:M2)

fungsi xlookup 26

Seperti yang Anda lihat, sintaksnya sama. itu hanya perbedaan antara dua rumus adalah Anda memasukkan kolom dalam pencarian vertikal saat Anda masuk baris dalam pencarian horizontal.

Contoh 6: Pencarian dua arah

Lakukan pencarian dua arah dengan XLOOKUP

Apakah Anda masih menggunakan Fungsi INDEX dan MATCH untuk mencari nilai dalam rentang dua dimensi? Mencoba XLOOKUP yang ditingkatkan untuk menyelesaikan pekerjaan Anda lebih mudah.

XLOOKUP dapat tampil pencarian ganda, menemukan sebuah persimpangan dari dua nilai. Oleh bersarang satu XLOOKUP di dalam yang lain, XLOOKUP di dalam dapat mengembalikan seluruh baris atau kolom, kemudian baris atau kolom yang dikembalikan ini dimasukkan ke dalam XLOOKUP luar sebagai larik kembali.

Misalkan Anda memiliki daftar nilai siswa dengan disiplin ilmu yang berbeda, Anda ingin mengetahui nilai mata pelajaran Kimia Kim.

fungsi xlookup 43

Mari kita lihat bagaimana kita menggunakan XLOOKUP ajaib untuk melakukan triknya.

    • Kami menjalankan XLOOKUP "dalam" untuk mengembalikan nilai kolom enter. XLOOKUP(H2,B1:E1,B2:E10) bisa mendapatkan berbagai nilai Kimia.
    • Kami menyarangkan XLOOKUP "dalam" di dalam XLOOKUP "luar" dengan menggunakan XLOOKUP "dalam" sebagai return_array dalam rumus lengkap.
    • Lalu inilah rumus terakhir:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

  • ketik rumus di atas ke dalam sel kosong H3, tekan tombol Enter tombol untuk mendapatkan hasilnya.

fungsi xlookup 27

Atau Anda bisa melakukannya sebaliknya, gunakan XLOOKUP "dalam" untuk mengembalikan nilai seluruh baris, yang semuanya merupakan nilai mata pelajaran Kim. Kemudian gunakan XLOOKUP “luar” untuk mencari nilai Kimia di antara semua nilai mata pelajaran Kim.

    • ketik di bawah rumus di sel kosong H4, dan tekan tombol Enter tombol untuk mendapatkan hasilnya.

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

fungsi xlookup 28

Fungsi pencarian dua arah XLOOKUP juga merupakan ilustrasi sempurna dari fungsi pencarian vertikal dan horizontal. Cobalah jika Anda mau!

Contoh 7: Sesuaikan pesan yang tidak ditemukan

Sesuaikan pesan tidak ditemukan menggunakan XLOOKUP

Sama seperti fungsi pencarian lainnya, ketika fungsi XLOOKUP tidak dapat menemukan kecocokan, yang #T/A pesan kesalahan akan dikembalikan. Mungkin membingungkan bagi beberapa pengguna Excel. Tapi kabar baiknya adalah penanganan kesalahan tersedia di argumen keempat dari fungsi XLOOKUP.

Dengan argumen [if_not_found] bawaan, Anda dapat menentukan pesan khusus untuk menggantikan hasil #N/A. Ketik teks yang Anda butuhkan dalam argumen keempat opsional dan sertakan teks di tanda kutip ganda (").

Misalnya, kota Denver tidak ditemukan, jadi XLOOKUP mengembalikan pesan kesalahan #N/A. Tapi setelah kita menyesuaikan argumen keempat dengan teks “No Match”, rumus akan menampilkan teks “No Match” sebagai ganti pesan kesalahan.

ketik di bawah rumus di sel kosong F3, dan tekan tombol Enter tombol untuk mendapatkan hasilnya.

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

fungsi xlookup 29

Sesuaikan kesalahan #N/A dengan fitur praktis

Untuk mengganti Kesalahan #N/A dengan cepat dengan pesan khusus Anda, Kutools untuk Excel is alat yang sempurna di Excel untuk membantu Anda. Dengan bawaannya Ganti 0 atau #T/A dengan Blank atau fitur Nilai Tertentu, Anda dapat menentukan pesan tidak ditemukan tanpa rumus rumit atau akses ke XLOOKUP.

Dengan kami Add-in Excel terinstal, lakukan hal berikut:

1. Pergi ke Kutools tab di Excel, temukan PENCARIAN Super, Dan klik Ganti 0 atau # N / A dengan Kosong atau Nilai Tertentu di daftar drop-down.

fungsi xlookup 30

2. Dalam Ganti 0 atau #N/A dengan Kosong atau kotak dialog Nilai Tertentu, Anda perlu mengonfigurasi sebagai berikut:

  • Dalam majalah Nilai pencarian dan bagian rentang Output, pilih rentang pencarian serta jangkauan keluaran;
  • Kemudian pilih opsi Ganti 0 atau #N/A dengan Nilai Tertentu, masukan teks kamu suka;
  • Dalam majalah Bagian rentang data, Pilih jangkauan data, lalu tentukan kolom kunci serta kolom yang dikembalikan.

fungsi xlookup 31

3. Klik OK tombol untuk mendapatkan hasilnya. Pesan yang disesuaikan akan ditampilkan ketika tidak ada kecocokan yang ditemukan.

fungsi xlookup 32

Klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari.


Contoh 8: Beberapa nilai

Kembalikan beberapa nilai dengan XLOOKUP

Lain keuntungan dari XLOOKUP adalah kemampuannya untuk kembalikan beberapa nilai pada saat yang sama untuk pertandingan yang sama. Masukkan satu rumus untuk mendapatkan hasil pertama, lalu nilai lain yang dikembalikan menumpahkan ke sel kosong tetangga secara otomatis.

Pada contoh di bawah ini, Anda ingin mendapatkan semua informasi tentang ID siswa “FG9940005”. Triknya adalah memberikan rentang sebagai return_array dalam rumus alih-alih satu kolom atau baris. Dalam hal ini, rentang larik yang dikembalikan adalah B2:D9, termasuk tiga kolom.

ketik di bawah rumus di sel kosong G2, tekan tombol Enter kunci untuk mendapatkan semua hasil.

=XLOOKUP(F2,A2:A9,B2:D9)

fungsi xlookup 33

Semua sel hasil menampilkan rumus yang sama. Kamu bisa mengedit atau memodifikasi rumusnya di sel pertama, tapi di sel lain, rumus tidak dapat diedit. Anda dapat melihat bilah rumus adalah berwarna abu-abu, artinya tidak ada perubahan yang dapat dilakukan untuk itu.

fungsi xlookup 34

Secara keseluruhan, fungsi multi-nilai XLOOKUP adalah a peningkatan yang bermanfaat dibandingkan dengan VLOOKUP. Anda bebas menentukan setiap nomor kolom secara terpisah untuk setiap rumus. Jempolan!

Contoh 9. Beberapa Kriteria

Lakukan pencarian multi-kriteria menggunakan XLOOKUP

Lain fitur baru yang luar biasa dari XLOOKUP adalah kemampuannya untuk pencarian dengan beberapa kriteria. Triknya adalah menggabungkan nilai pencarian dan array pencarian dengan operator "&" secara terpisah dalam rumus. Mari kita ilustrasikan melalui contoh di bawah ini.

Kita perlu tahu harga vas medium biru. Dalam hal ini, tiga nilai pencarian (kriteria) diperlukan untuk mencari kecocokan. Ketik rumus di bawah di sel kosong I2, lalu tekan tombol Enter kunci untuk mendapatkan hasil.

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

fungsi xlookup 35

Catatan: XLOOKUP dapat langsung memproses array. Tidak perlu mengkonfirmasi rumus dengan Control + Shift + Enter.

Pencarian multi-kondisi dengan metode cepat

Apakah ada lebih cepat dan lebih mudah cara melakukan pencarian multi-kriteria daripada XLOOKUP di excel? Kutools untuk Excel menyediakan fitur yang luar biasa - Pencarian multi-kondisi. Dengan fitur ini, Anda dapat menjalankan pencarian beberapa kriteria hanya dengan beberapa klik!

Dengan kami Add-in Excel terinstal, lakukan hal berikut:

1. Pergi ke Kutools tab di Excel, temukan PENCARIAN Super, Dan klik Pencarian multi-kondisi di daftar drop-down.

fungsi xlookup 36

2. Dalam Kotak dialog Pencarian Multikondisi, lakukan hal berikut:

  • Dalam majalah Nilai Pencarian dan bagian Rentang Output, Pilih rentang nilai pencarian dan jangkauan keluaran;
  • Dalam majalah Bagian Rentang Data, lakukan operasi berikut:
    • Pilih kolom kunci yang sesuai yang berisi nilai pencarian satu per satu dengan menahan tombol Ctrl kunci di Kotak kolom kunci;
    • Tentukan kolom yang berisi nilai yang dikembalikan dalam Kembalikan kotak kolom.

fungsi xlookup 37

3. Klik OK tombol untuk mendapatkan hasilnya.

fungsi xlookup 38

Catatan:

  • Bagian Ganti nilai kesalahan #N/A dengan nilai yang ditentukan adalah opsional di kotak dialog, Anda dapat menentukannya atau tidak.
  • Jumlah kolom yang dimasukkan dalam kotak kolom Kunci harus sama dengan jumlah kolom yang dimasukkan dalam kotak Nilai Pencarian, dan urutan kriteria di kedua kotak harus sesuai satu sama lain.

Klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari.


Contoh 10. Temukan Nilai dengan Pertandingan Terakhir

Dapatkan hasil pencocokan terakhir dengan XLOOKUP

Untuk menemukan nilai pencocokan terakhir di Excel, atur argumen keenam dalam fungsi XLOOKUP untuk cari dalam urutan terbalik.

Secara default, mode pencarian di XLOOKUP diatur ke 1, Yang cari dari pertama sampai terakhir. Tapi hal yang baik tentang XLOOKUP adalah bahwa arah pencarian dapat diubah. XLOOKUP menawarkan argumen opsional [mode pencarian] untuk mengontrol urutan pencarian. Cukup atur mode pencarian di argumen keenam ke -1, arah pencarian diubah menjadi pencarian dari terakhir ke pertama.

Silakan lihat contoh di bawah ini. Kami ingin tahu penjualan terakhir Emma di database.

ketik rumus di bawah di sel kosong G2, lalu tekan tombol Enter kunci untuk mendapatkan hasil.

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

fungsi xlookup 39

Catatan: Argumen keempat dan kelima adalah opsional dan dihilangkan dalam kasus ini. Kami hanya mengatur argumen keenam opsional ke -1.

Mudah mencari nilai pencocokan terakhir dengan alat yang luar biasa

Jika Anda tidak memiliki akses ke XLOOKUP dan juga tidak ingin mengingat rumus yang rumit, Anda dapat menerapkan Cari dari fitur Bawah ke Atas of Kutools untuk Excel untuk menyelesaikannya mudah.

Dengan kami Add-in Excel terinstal, lakukan hal berikut:

1. Pergi ke Kutools tab di Excel, temukan PENCARIAN Super, Dan klik Cari dari Bawah ke Atas di daftar drop-down.

fungsi xlookup 40

2. Dalam LOOKUP dari Bawah ke Atas kotak dialog, Anda perlu mengonfigurasi sebagai berikut:

  • Dalam majalah Nilai pencarian dan bagian rentang Output, Pilih rentang pencarian serta jangkauan keluaran;
  • Dalam majalah Bagian rentang data, Pilih jangkauan data, lalu tentukan kolom kunci serta kolom kembali.

fungsi xlookup 41

3. Klik OK tombol untuk mendapatkan hasilnya.

fungsi xlookup 42

Catatan: Ganti nilai kesalahan #N/A dengan bagian nilai yang ditentukan adalah opsional di kotak dialog, Anda dapat menentukannya atau tidak.

Klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari.


Unduh File Contoh XLOOKUP

Contoh XLOOKUP.xlsx

Artikel terkait:


  • 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 (0)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
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