Gunakan INDEX dengan dua fungsi MATCH yang cocok persis untuk mengembalikan jumlah pada pertemuan baris aset dan kolom format. Panduan ini membuat lembar kerja kecil untuk aset seni, menjelaskan rumusnya, dan menunjukkan alternatif XLOOKUP bertingkat untuk versi Mac yang didukung.

Metode pencarian mana yang berfungsi di Excel untuk Mac?

Untuk tugas ini, Anda memiliki nama aset, seperti Dot Grid, dan format, seperti PNG. Anda ingin mendapatkan jumlah tunggal pada titik pertemuan keduanya. Ini biasa disebut pencarian dua arah.

Microsoft mencantumkan INDEX dan MATCH sebagai fungsi yang tersedia di Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, dan Excel 2021 untuk Mac. Kedua metode dalam artikel ini berfungsi dalam cakupan Mac yang terdokumentasi tersebut. Lihat dokumentasi fungsi INDEX dan dokumentasi fungsi MATCH dari Microsoft.

XLOOKUP juga didokumentasikan untuk edisi Mac tersebut. Microsoft secara eksplisit menyatakan bahwa fungsi ini tidak tersedia di Excel 2016 dan Excel 2019, meskipun kedua edisi itu tercantum dalam daftar penerapan yang lebih luas pada halaman tersebut. Jika edisi Anda tidak memiliki XLOOKUP, gunakan metode INDEX dan MATCH. Microsoft: fungsi XLOOKUP

Mulailah dengan INDEX dan MATCH jika Anda ingin memeriksa posisi baris dan kolom secara terpisah sambil belajar. Pilih XLOOKUP bertingkat jika Anda sudah menggunakan fungsi tersebut dan menyukai pencocokan persis sebagai pengaturan default.

Siapkan kisi jumlah aset seni sederhana

Di lembar kerja kosong, masukkan data sintetis berikut di A1:D5. Kolom tabel paling kiri di bawah mewakili kolom lembar kerja A; jangan tambahkan kolom ekstra untuk nomor baris. Jumlah ini hanya sebagai ilustrasi.

Aset SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Setiap aset menempati satu baris, dan setiap format menempati satu kolom. Untuk contoh ini, gunakan label aset dan tajuk format yang unik. Masukkan angka nol secara eksplisit jika jumlahnya memang nol; periksa jumlah yang belum dipastikan sebelum menggunakan kisi ini.

Buat area pencarian terpisah di lembar kerja yang sama:

Sel Masukkan
F1 Aset yang dicari
F2 Dot Grid
G1 Format yang dicari
G2 PNG
H1 Jumlah

Ketiga rentang sumber memiliki fungsi berbeda: A2:A5 berisi label aset, B1:D1 berisi tajuk format, dan B2:D5 hanya berisi jumlah. Dengan menempatkan tajuk di luar rentang jumlah, posisi relatifnya akan sejajar dengan benar.

Kembalikan jumlah dengan INDEX dan dua fungsi MATCH

Pilih H2, masukkan rumus lengkap berikut, lalu tekan Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Hasilnya seharusnya 24: jumlah untuk Dot Grid di kolom PNG. Petunjuk Microsoft untuk Mac menjelaskan cara memilih sel hasil, memasukkan rumus yang diawali =, lalu menekan Return. Microsoft: Memasukkan rumus

Pahami kedua posisi

Fungsi MATCH pertama mencari posisi aset dalam A2:A5; fungsi kedua mencari posisi format dalam B1:D1. Argumen terakhir 0 meminta pencocokan persis. Tanpa argumen itu, MATCH menggunakan pencocokan perkiraan secara default dan mensyaratkan urutan tertentu. Microsoft: fungsi MATCH

Untuk masukan yang diberikan, kedua perhitungan di dalamnya adalah:

=MATCH(F2,$A$2:$A$5,0)

Hasil: 2.

=MATCH(G2,$B$1:$D$1,0)

Hasil: 2.

Karena itu, rumus lengkap memilih baris 2, kolom 2 dalam blok jumlah:

=INDEX($B$2:$D$5,2,2)

Itulah sel lembar kerja C3, yang berisi 24. INDEX menggunakan posisi dalam rentang yang diberikan, jadi baris keduanya adalah baris lembar kerja 3 di sini. Microsoft mendokumentasikan bahwa memberikan kedua posisi akan mengembalikan nilai pada titik pertemuannya. Microsoft: fungsi INDEX

Pertahankan rentang sumber saat menyalin

Tanda dolar mengunci rentang sumber. Referensi masukan F2 dan G2 tetap relatif, jadi menyalin H2 ke bawah hingga H3 membuat rumus berikutnya membaca F3 dan G3 sambil menggunakan kisi sumber yang sama. Di Mac, Anda juga dapat memilih referensi di bilah rumus lalu menekan Command+T untuk mengganti jenis referensi. Microsoft: Beralih antara referensi relatif dan absolut

Misalnya, masukkan Tile Arc di F3 dan JPG di G3, lalu salin H2 ke H3. Hasil baru seharusnya 6. Dengan begitu, Anda memiliki daftar permintaan sederhana tanpa mengubah kisi aset asli.

Gunakan XLOOKUP bertingkat di versi Mac yang didukung

Dengan lembar kerja dan masukan pencarian yang sama, Anda dapat memasukkan rumus berikut di H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

Rumus ini mengembalikan 24 untuk Dot Grid dan PNG. Dokumentasi Microsoft menyertakan contoh XLOOKUP bertingkat yang melakukan pencarian vertikal dan horizontal untuk mengembalikan nilai pada titik pertemuan. Pencocokan persis menjadi pengaturan default. Microsoft: fungsi XLOOKUP

Dalam contoh ini, pencarian di dalamnya memilih kolom jumlah PNG: 18, 24, 9, dan 14. Pencarian di luarnya memilih entri yang sejajar dengan Dot Grid. Menggunakan tiga rentang sumber yang sama memudahkan Anda membandingkan metode ini dengan rumus sebelumnya.

Periksa hasil sebelum menggunakan kisi yang lebih besar

Gunakan kasus berikut sebagai pemeriksaan penerimaan sederhana. Ubah F2 dan G2 untuk setiap baris, lalu bandingkan H2 dengan hasil yang diharapkan. Setelah itu, kembalikan ke Dot Grid dan PNG jika Anda ingin mempertahankan contoh awal.

Masukan aset Masukan format Hasil yang diharapkan Hal yang diperiksa
Dot Grid PNG 24 Titik pertemuan tengah
Tile Arc JPG 6 Aset pertama dan format terakhir
Soft Square SVG 7 Aset terakhir dan format pertama
Loop Ribbon JPG 0 Jumlah nol eksplisit
Tile Arcs PNG #N/A Label aset tidak ditemukan
Dot Grid TIFF #N/A Tajuk format tidak ditemukan

Jika angkanya salah, periksa batas rentang sebelum mengubah metode pencarian. Untuk kisi ini, A2:A5 harus sejajar dengan empat baris B2:D5, dan B1:D1 harus sejajar dengan tiga kolomnya. Misalnya, menyertakan A1 hanya dalam rentang pencarian aset akan menggeser posisi hasil perhitungan dari baris jumlah yang dimaksud.

Saat memperluas kisi ini dengan aset lain di baris 6, perbarui rentang aset menjadi A2:A6 dan blok jumlah menjadi B2:D6. Saat menambahkan format di kolom E, perbarui rentang tajuk menjadi B1:E1 dan blok jumlah menjadi B2:E5. Perubahan berpasangan ini mempertahankan tata letak yang digunakan rumus.

Label duplikat juga perlu diperhatikan. MATCH dengan pencocokan persis mengembalikan posisi pertama yang cocok dan tidak membedakan huruf besar-kecil; fungsi ini juga menafsirkan * dan ? sebagai karakter pengganti dalam pencarian teks. Untuk pengaturan ini, gunakan label unik yang tidak memuat karakter tersebut. Microsoft: fungsi MATCH

Tampilkan pesan yang berguna jika label tidak ditemukan

Setelah pencarian dasar berfungsi, Anda dapat mengganti kesalahan karena hasil tidak ditemukan dengan pesan yang mudah dibaca:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA mengembalikan pesan Anda ketika rumus yang diapit menghasilkan #N/A; jika tidak, fungsi ini mempertahankan hasilnya. Fungsi ini terdokumentasi untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, dan Excel 2021 untuk Mac. Microsoft: fungsi IFNA

Dalam contoh ini, Tile Arcs menghasilkan Periksa aset atau format, sedangkan Loop Ribbon dengan JPG tetap mengembalikan 0. Dengan begitu, label yang tidak ditemukan tetap dapat dibedakan dari jumlah nol yang tercatat. Jika jumlah sumber itu sendiri berisi #N/A, pembungkus ini juga akan mengganti kesalahan tersebut, jadi periksa sel sumber jika labelnya tampak benar.

Untuk penggunaan sehari-hari, buat nama aset yang unik, pertahankan keselarasan ketiga rentang sumber, dan periksa beberapa titik pertemuan yang nilainya diketahui setelah mengubah kisi. Setelah itu, Anda dapat mengambil jumlah setiap aset seni yang diminta dengan mengubah masukan aset dan format.

Sumber