4 Cara Mengambil Data Tabel dengan Kriteria Mendatar dan Menurun dengan Mudah

Microsoft Excel adalah alat yang sangat kuat untuk menangani data dalam berbagai bentuk, salah satunya adalah tabel dengan kriteria yang mendatar (horizontal) dan menurun (vertikal). Dalam beberapa kasus, Anda mungkin perlu mencari data dalam tabel besar yang memerlukan pencocokan baik secara mendatar maupun menurun. Pada artikel ini, kita akan membahas cara mengambil data tabel yang kompleks, menggunakan fungsi-fungsi Excel seperti VLOOKUP, HLOOKUP, serta kombinasi INDEX dan MATCH.

4 Cara Mengambil Data Tabel dengan Kriteria

Sebelum ke cara mengambil data tabel dengan kriteria mendatar dan menurun di Excel, agar memudahkan, 4 cara ini akan menggunakan tabel yang sama dengan skenario yang sama pula yakni kita ingin mencari penjualan wiraniaga atas nama “SAKURA HARUNO” di bulan “APRIL.”

Dan berikut adalah bentuk tabel datanya:

1. Menggunakan Kombinasi VLOOKUP dan MATCH

Salah satu cara mengambil data tabel paling umum untuk mencari data berdasarkan kriteria mendatar dan menurun adalah menggunakan kombinasi VLOOKUP dan MATCH. Fungsi VLOOKUP biasanya digunakan untuk pencarian vertikal, sementara MATCH dapat digunakan untuk mencari posisi kolom tertentu secara horizontal.

Berikut adalah langkah-langkahnya:

1. Gunakan VLOOKUP untuk mencari produk di kolom pertama.
2. Gunakan MATCH untuk menemukan posisi bulan yang diinginkan di baris atas.

Berikut adalah rumusnya:

=VLOOKUP(C11;B3:H9;MATCH(C12;B2:H2;0);0)

Penjelasan Rumus

Berikut penjelasan singkat rumus di atas:

• VLOOKUP(C11;B3:H9;…): Mencari nilai di C11 dalam kolom pertama (B3:B9).
• MATCH(C12;B2:H2;0): Menemukan posisi kolom di rentang B2:H2 yang sesuai dengan nilai di C12.
• 0: Menandakan pencarian kecocokan yang tepat (exact match).

Cara kerja: Rumus ini mencari baris sesuai dengan C11 dan kolom sesuai dengan C12, lalu mengembalikan nilai dari perpotongan baris dan kolom tersebut. Dan jika rumus ini diinput dengan benar maka hasilnya adalah “6.034.000” sesuai dengan data pada tabel.

2. Menggunakan Kombinasi INDEX dan MATCH

Kombinasi INDEX dan MATCH sering kali lebih fleksibel daripada VLOOKUP, terutama ketika Anda perlu melakukan pencarian mendatar dan menurun. INDEX digunakan untuk mengambil nilai dari tabel berdasarkan posisi baris dan kolom, sedangkan MATCH digunakan untuk menentukan posisi dari nilai yang Anda cari.

Sintaks Dasar

=INDEX(rentang_hasil, MATCH(kriteria_vertikal, rentang_vertikal, 0), MATCH(kriteria_horizontal, rentang_horizontal, 0))

Contoh Kasus:

Menggunakan tabel yang sama seperti sebelumnya, kita ingin ingin mencari penjualan wiraniaga atas nama “SAKURA HARUNO” di bulan “APRIL.”

Rumusnya adalah sebagai berikut:

=INDEX(C3:H9;MATCH(C11;B3:B9;0); MATCH(C12;C2:H2;0))

Penjelasan Rumus:

• INDEX(C3:H9; ...) adalah rentang dari tabel yang berisi data yang kita cari.
• MATCH(C11;B3:B9;0) menemukan baris di mana “SAKURA HARUNO” berada.
• MMATCH(C12;C2:H2;0) menemukan kolom yang sesuai dengan di C12 yakni “APRIL.”

Hasilnya: Rumus ini akan mengembalikan nilai “6.034.000”, yang merupakan nilai transaksi wiraniaga “SAKURA HARUNO” di bulan “APRIL.”

3. Menggunakan Fungsi HLOOKUP dan VLOOKUP Bersama

Fungsi HLOOKUP dan VLOOKUP juga dapat digunakan secara bersamaan jika Anda memiliki tabel yang memerlukan pencarian mendatar dan menurun. VLOOKUP digunakan untuk pencarian vertikal, sementara HLOOKUP digunakan untuk pencarian horizontal.

Berikut adalah rumusnya:

=HLOOKUP(C12;B2:H9; MATCH(C11;B2:B9;0);FALSE)

Penjelasan Rumus:

• HLOOKUP(C12;B2:H9; ...) mencari “April” di baris pertama tabel.
• MATCH(C11;B2:B9;0) menentukan baris di mana “SAKURA HARUNO” berada.

Hasilnya: Rumus ini akan mengembalikan nilai “6.034.000”, yang merupakan nilai transaksi wiraniaga “SAKURA HARUNO” di bulan “APRIL.”

4. Menggunakan SUMPRODUCT untuk Pencarian Multi-Kriteria

Fungsi SUMPRODUCT juga bisa digunakan untuk pencarian berdasarkan beberapa kriteria mendatar dan menurun. Ini sering kali lebih efektif jika Anda bekerja dengan tabel besar yang memerlukan beberapa kondisi atau kriteria.

Sintaks Dasar:

=SUMPRODUCT((kriteria1)*(kriteria2)*(kolom_hasil))

Contoh Kasus:

Menggunakan tabel yang sama seperti sebelumnya, kita ingin ingin mencari penjualan wiraniaga atas nama “SAKURA HARUNO” di bulan “APRIL.”. Maka rumus SUMPRODUCT yang bisa digunakan adalah sebagai berikut:

=SUMPRODUCT((B3:B9=C11)*(C2:H2=C12)*(C3:H9))

Penjelasan Rumus:

• (B3:B9=C11) mencari baris yang sesuai dengan nama “SAKURA HARUNO1”.
• (C2:H2=C12) mencari kolom yang sesuai dengan bulan “APRIL.”
• (C3:H9) adalah rentang nilai yang akan dikembalikan.

Hasilnya: Rumus ini akan mengembalikan nilai “6.034.000”, yang merupakan nilai transaksi wiraniaga “SAKURA HARUNO” di bulan “APRIL.”

Kesimpulan

Mengambil data dari tabel dengan kriteria mendatar dan menurun di Microsoft Excel sangat mudah jika Anda tahu alat yang tepat. Dengan kombinasi fungsi VLOOKUP, HLOOKUP, INDEX, MATCH, dan bahkan SUMPRODUCT, Anda bisa melakukan pencarian data yang kompleks dengan efisien. Setiap fungsi memiliki kelebihannya masing-masing, jadi pilihlah yang paling sesuai dengan kebutuhan Anda.

Dengan menguasai teknik-teknik ini, Anda dapat menangani tabel besar dan data yang rumit dengan lebih mudah dan cepat di Microsoft Excel. Selamat mencoba!

Pranala Luar

File Excel 4 Cara Mengambil Data Tabel dengan Kriteria Mendatar dan Menurun di Microsoft Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel dengan judul video: Cara Mengambil Data Tabel dengan Kriteria Mendatar dan Menurun. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!