Bagaimana cara membalikkan vlookup di excel dengan index-match?

Untuk memahami cara kerja INDEX dan MATCH sebagai rumus pencarian terbalik, Anda perlu memilih satu hal sederhana: Match memberi tahu indeks posisi (nomor sel) suatu nilai dalam kolom atau baris, lalu indeks mengembalikan nilai ini. nilai menggunakan posisi ini (nomor sel) .

Anggap saja seperti ini, fungsi MATCH adalah agen rahasia yang menemukan penjahat dan fungsi INDEX adalah polisi yang kemudian menangkap penjahat tersebut.

Tapi, mari kita pelajari secara detail bagaimana kita bisa menggabungkan kedua fungsi ini. Di bawah ini adalah sintaks INDEX seperti yang Anda ketahui.

INDEKS(array, angka_baris, [angka_kolom])

Dalam fungsi INDEX, argumen row_num memberitahukannya dari baris mana ia harus mengembalikan nilainya. Katakanlah jika Anda memasukkan 4, maka akan mengembalikan nilai baris ke-4.

Untuk membuat rumus pencarian terbalik, kita perlu mengganti MATCH dengan argumen_baris dari INDEX.

Saat kita menggunakan MATCH, ia mencari nilai di kolom pencarian dan mengembalikan nomor sel dari nilai tersebut. Lalu, INDEX menggunakan nomor ini untuk menentukan posisi sel dari kolom nilai.

Pada akhirnya ia mengembalikan nilai sel itu dan Anda mendapatkan nilai yang Anda cari. Namun sekarang mari kita bekerja dengan contoh nyata. Di bawah ini kami memiliki daftar kota dan nama karyawan yang bekerja di sana.

Disini kita perlu mencari nama karyawan yang bekerja di Mumbai. Sekarang, jika Anda melihat datanya, di kolom yang berisi kota, itulah kolom pencarian kami, dan di kolom yang berisi nama karyawan, itulah kolom nilai Anda. Dan rumusnya menjadi:

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Mari kita bagi rumus ini menjadi dua bagian untuk memahaminya.

Bagian 1 : Pada bagian pertama, kami menggunakan fungsi pencocokan untuk mencari nilai “Mumbai” dan mengembalikan “5” yang merupakan posisi sel di mana Anda memiliki nilai “Mumbai” di kolom kota.

Bagian 2 : Pada bagian kedua, kita menggunakan INDEX dan mengacu pada kolom nama karyawan untuk mencari nilainya. Di sini fungsi indeks mengetahui bahwa Anda menginginkan nilai sel ke-5 kolom. Jadi, hasilnya adalah “Siya”.

Lebih Banyak Contoh INDEX dan Pencocokannya

Di hadapan kita terdapat beberapa permasalahan umum yang telah kita selesaikan dengan menggunakan rumus INDEX MATCH. Akses file sampel: Pastikan untuk mengunduh file sampel ini dari sini untuk mengikuti setiap contoh.

1. Pencarian dasar dengan INDEX – MATCH

Pencarian normal adalah salah satu tugas terpenting yang perlu Anda lakukan dengan rumus pencarian dan INDEX MATCH sangat cocok untuk itu. Di sini kita memiliki tabel data dengan ID dan nama karyawan. Setiap ID unik dan Anda perlu mencari nama karyawan dengan ID mereka.

Katakanlah Anda ingin mencari nama EMP-132. Untuk ini, rumusnya adalah:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

beginilah cara kerja rumus ini

Pertama , MATCH mencocokkan id emp di kolom id emp dan mengembalikan nomor sel dari id yang Anda cari. Di sini nomor barisnya adalah 6.

Setelah itu INDEX mengembalikan nama karyawan dari kolom nama menggunakan nomor sel yang sama.

2. Lihat ke kiri

VLOOKUP tidak bisa ke kiri saat mencari nilai. Seperti yang saya sebutkan, di INDEX dan MATCH Anda dapat mencari ke segala arah. Pada tabel data di bawah ini Anda memiliki kolom nomor faktur setelah kolom jumlah.

Jadi jika Anda ingin mencari jumlah tagihan tertentu, hal ini tidak dapat dilakukan dengan VLOOKUP. Di VLOOKUP ketika Anda memilih tabel, kolom pertama dalam tabel tersebut akan menjadi kolom pencarian.

Namun, di tabel ini, kita perlu menggunakan kolom terakhir tabel sebagai kolom pencarian. Jadi jempol ke bawah untuk VLOOKUP di sini. Mari kita panggil INDEX dan MATCH untuk penyelamatan dan rumusnya adalah:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…begitulah cara kerja rumus ini

• Pertama-tama Anda mengacu pada kolom jumlah di fungsi indeks. Ini adalah kolom yang kita perlukan untuk mendapatkan nilainya.
• Kedua , dalam argumen nomor_baris pada fungsi indeks, Anda menggunakan fungsi pencocokan dan menentukan nomor faktur, merujuk ke kolom faktur, dan menggunakan nol untuk pencocokan tepat.
• Ketiga , fungsi pencocokan mengembalikan nomor sel faktur dari rentang tersebut.

Dan pada akhirnya, INDEX menggunakan nomor ini untuk mengembalikan jumlah dengan memposisikan sel dari kolom jumlah.

3. Pencarian Fuzzy

Sama seperti VLOOKUP, Anda juga dapat menggunakan INDEX/MATCH untuk pencarian kasar.

Pencarian fuzzy dapat berguna ketika nilai yang Anda cari tidak tercantum dan Anda ingin mendapatkan hasil yang paling mendekati. Pada tabel di bawah ini Anda memiliki daftar nilai berdasarkan nilai.

Dan jika ingin mendapatkan skor 79 bisa menggunakan rumus dibawah ini.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…begitulah cara kerja rumus ini

Dalam rumus ini, kami telah menggunakan 1 dalam fungsi pencocokan untuk tipe_pencocokan yang memungkinkannya melakukan perkiraan pencarian. Ini mengembalikan nilai pertama yang kurang dari atau sama dengan nilai pencarian.

Untuk 79, nilai terendah pertama adalah 75, dan untuk 75, nilainya adalah B. Itu sebabnya Anda mendapatkan hasil B.

4. PENCARIAN Horisontal

Seperti yang kamu ketahui, HLOOKUP ditujukan untuk pencarian horizontal, namun kamu juga bisa menggunakan INDEX dan MATCH untuk itu. Di sini, di tabel data di bawah ini Anda memiliki tabel horizontal untuk penjualan bulanan dan Anda ingin mendapatkan nilai penjualan untuk “Mei”.

Dan rumusnya menjadi:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…begitulah cara kerja rumus ini

Dalam rumus di atas, alih-alih menggunakan MATCH di argumen nomor_baris indeks, kami menggunakannya di nomor_kolom. Dan, kecocokan mengembalikan nomor kolom Mei.

Dan kemudian INDEX mengembalikan nilai kolom hasil berdasarkan nomor posisi.

5. Pencarian dua arah

Dalam pencarian dua arah, kita perlu mendapatkan nilai dari sebuah tabel. Lihat saja tabel di bawah ini di mana Anda mengetahui jumlah penjualan berdasarkan area dan produk.

Sekarang jika Anda ingin mendapatkan jumlah penjualan suatu produk untuk suatu wilayah tertentu Anda memerlukan pencarian dua arah dan untuk itu Anda perlu menggunakan kombinasi INDEX MATCH MATCH. Ya, di sini Anda harus menggunakan MATCH dua kali.

Dalam kombinasi normal INDEX dan MATCH, Anda menggunakan MATCH untuk nomor baris, namun dalam pencarian dua arah Anda juga harus menggunakannya untuk nomor kolom. Rumusnya adalah:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…begitulah cara kerja rumus ini

Dalam rumus di atas, untuk mendapatkan jumlah penjualan dari tabel, Anda menggunakan fungsi indeks dan kemudian fungsi pencocokan untuk argumen jumlah_baris dan jumlah_kolomnya. Fungsi pencocokan yang merupakan argumen jumlah_kolom mengembalikan 5 sebagai nilai Produk-D di baris ke-5 dari rentang yang Anda referensikan.

Dan, fungsi pencocokan yang ditemukan dalam argumen row_num mengembalikan 2 karena nilai zona utara berada di kolom kedua dari rentang yang Anda referensikan.

Sekarang, dengan nilai-nilai ini, fungsi indeks mengembalikan nilai yang ada di kolom ke-2 dan baris ke-5: 1456.

6. Peka huruf besar/kecil

Jika Anda mengalami masalah ketika Anda memiliki dua nilai yang sama dalam daftar atau kolom tetapi dalam huruf besar/kecil yang berbeda, Anda dapat melakukan pencarian peka huruf besar-kecil untuk menemukan nilai yang benar. Mari kita lihat daftar siswa di bawah ini yang mana nama depannya ada pada anda dan pada kolom kedua sudah anda tandai.

Dan pada bagian awal terdapat kata benda yang sama namun dalam kasus teks yang berbeda. Misalnya saja John Parker dan JOHN Mathew. Katakanlah Anda ingin mencari merek “JOHN” dan bukan “John”, Anda dapat membuat pencarian pencocokan tepat dengan INDEX dan MATCH. Dan rumusnya menjadi:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…begitulah cara kerja rumus ini

Di sini, di rumus ini Anda telah menggunakan fungsi EXACT dalam fungsi pencocokan. Karena fungsi pencocokan tidak dapat mencari nilai peka huruf besar-kecil dan EXACT adalah fungsi yang sempurna untuk ini.

Itu dapat membandingkan dua nilai dan mengembalikan TRUE jika keduanya persis sama (termasuk huruf besar/kecil), tetapi Anda harus memasukkan rumus ini dalam bentuk tabel karena Anda perlu membandingkan seluruh kolom dengan satu nilai di EXACT. Saat Anda memasukkannya, ia akan mengembalikan array seperti ini.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

Dari sana Anda perlu menggunakan fungsi match untuk mendapatkan posisi TRUE dari array.

Pada titik ini Anda memiliki nomor sel (nomor baris) dari nilai yang perlu Anda temukan. Dan, Anda dapat menggunakan INDEX untuk nilai kolom ini.

Poin penting: Jika Anda mendapatkan lebih dari satu BENAR dengan EXACT, pencocokan hanya akan mengembalikan angka BENAR pertama

7. Dengan wildcard dengan INDEX MATCH

Wildcard sangat berguna. Anda dapat melakukan pencarian parsial menggunakan wildcard . Dan, bagian terbaiknya adalah seperti semua pencarian rumus lainnya, Anda juga dapat menggunakan wildcard dengan indeks dan pencocokan.

Lihat saja daftar nama di bawah ini yang berisi nama depan dan belakang karyawan serta usia mereka.

Dari daftar ini Anda perlu mengetahui usia karyawan tertentu (Sondra). Namun faktanya Anda hanya mengetahui nama depannya saja.

Dan jika menggunakan tanda bintang, Anda bisa mencari umur Sondra menggunakan nama depannya. Untuk ini, rumusnya adalah:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

…begitulah cara kerja rumus ini

Tanda bintang adalah karakter wildcard yang dapat menggantikan sejumlah n karakter. Jadi bila Anda menggunakannya setelah nama depan, itu menggantikan nama belakang.

8. Nilai terendah

Katakanlah Anda mempunyai daftar siswa dengan nilai mereka seperti di bawah ini. Dan sekarang dari daftar ini Anda ingin mencari nama siswa yang memiliki nilai terendah.

Untuk ini, Anda dapat menggunakan fungsi MIN dengan indeks dan pencocokan dan rumusnya adalah:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Anda mempunyai nama siswa di kolom A dan nilai mereka di kolom B.

Jadi ketika rumus ini dimasukkan ke dalam sel dan tekan Enter, maka akan muncul nama siswa dengan nilai terendah yaitu Librada Bastian.

Penjelasan

Dalam rumus ini kami memiliki tiga bagian berbeda.

Pada bagian pertama , fungsi MIN mengembalikan skor terendah.

Setelah itu, di bagian kedua, fungsi pencocokan mengembalikan sel untuk skor terendah tersebut.

Pada akhirnya, fungsi indeks mengembalikan nilai kolom nama siswa menggunakan posisi sel yang sama yang dikembalikan dengan kecocokan.

Tip: Dengan cara yang sama, Anda juga bisa mendapatkan nama siswa yang mendapat nilai tertinggi.

9. Skor ke-n terbaik

Sekarang pikirkan seperti ini, Anda memiliki daftar siswa beserta nilai ujiannya dan dari daftar ini Anda ingin mendapatkan nama siswa yang mendapat nilai tertinggi ke-2.

Soalnya, Anda tidak tahu berapa skor tertinggi kedua.

Biasanya, ketika Anda mencari suatu nilai dengan pencarian rumus, Anda yakin dengan nilai yang Anda cari. Tapi, di sini Anda belum tahu berapa skor tertinggi kedua.

Jadi untuk ini Anda bisa menggabungkan fungsi besar dengan indeks dan mencocokkannya. Fungsi besar akan membantu Anda menentukan nilai tertinggi kedua dalam rentang tersebut.

Dan rumusnya menjadi:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…begitulah cara kerja rumus ini

Dalam rumus ini, Anda telah menggunakan fungsi besar di dalam fungsi kecocokan untuk argumen nilai_pencarian. Dan pada fungsi besar Anda menyebutkan rentang skor dan 2 untuk mendapatkan nilai tertinggi kedua.

Setelah fungsi besar mengembalikan nilai terbesar kedua, fungsi pencocokan menggunakan nilai tersebut dan mengembalikan nomor sel yang sesuai.

Dan pada akhirnya, fungsi indeks menggunakan nomor sel ini dan mengembalikan nama siswa.

10. Berbagai kriteria

Biasanya kombinasi indeks dan pencocokan dimaksudkan untuk mencari satu nilai. Dan itulah mengapa Anda hanya menggunakan satu rentang dalam fungsi pencocokan.

Namun, terkadang saat Anda dihadapkan pada data di dunia nyata, Anda perlu menggunakan beberapa kriteria untuk menemukan suatu nilai.

Perhatikan contoh di bawah ini. Di sini Anda memiliki daftar produk dengan beberapa detail seperti nama produk, kategori dan ukuran.

Dan dari data tersebut Anda ingin mendapatkan harga suatu produk tertentu dengan menggunakan semua kriteria.

Oleh karena itu, rumusnya adalah:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Catatan: Ini adalah rumus array, jadi Anda harus memasukkannya menggunakan ctrl + shift + enter.

…begitulah cara kerja rumus ini

Dalam rumus ini Anda memiliki tiga larik berbeda untuk mencocokkan tiga nilai berbeda dan larik ini mengembalikan TRUE dan FALSE jika nilainya cocok.

Setelah itu ketika Anda mengalikannya satu sama lain, Anda mendapatkan array atau semacamnya.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

Dan fungsi match mengembalikan posisi 1 untuk array.

Pada akhirnya, INDEX mengembalikan harga kolom harga menggunakan nomor yang dikembalikan oleh kecocokan.

Tip: Jika Anda tidak ingin menggunakan rumus array, Anda bisa menggunakan kondisi SUMPRODUK .

11. Nilai numerik pertama dari suatu rentang

Katakanlah Anda memiliki daftar di mana Anda memiliki nilai teks dan numerik, dan sekarang dari daftar ini Anda ingin mendapatkan nilai numerik pertama.

Untuk melakukan ini, Anda dapat menggabungkan fungsi ISNUMBER dengan indeks/kecocokan. ISNUMBER dapat membantu Anda mengidentifikasi mana nilai yang berupa angka dan mana yang berupa teks.

Rumusnya adalah:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

Anda harus memasukkan rumus ini dalam bentuk tabel (menggunakan Ctrl + Shift + Enter).

…begitulah cara kerja rumus ini

Dalam rumus ini ISNUMBER mengembalikan array yang sama dengan panjang daftar dan dalam array ini Anda memiliki TRUE untuk nilai yang berupa angka dan FALSE untuk nilai lainnya.

Setelah itu Anda menggunakan TRUE dalam fungsi pencocokan sebagai nilai pencarian. Jadi ia mengembalikan nomor posisi TRUE pertama dalam array.

Pada akhirnya, penggunaan indeks nomor posisi ini akan mengembalikan nilai numerik pertama.

12. Dapatkan nilai pertama yang tidak kosong

Mari kita berpikir seperti ini, Anda memiliki daftar nilai yang beberapa sel pertamanya kosong dan Anda ingin mendapatkan nilai pertama yang tidak kosong.

Dan, Anda bisa menggunakan rumus ini untuk mendapatkan nilai tak kosong pertama ini.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…begitulah cara kerja rumus ini

Kita perlu membagi rumus ini menjadi tiga bagian berbeda untuk memahaminya dengan bijak.

Pertama , Anda menggunakan fungsi ISBLANK dalam fungsi pencocokan untuk mendapatkan array yang memiliki TRUE untuk sel kosong dan FALSE untuk sel tidak kosong.

Kedua , MATCH mengembalikan nomor posisi TRUE pertama dalam array yang dikembalikan oleh ISBLANK.

Jadi pada titik ini Anda memiliki nomor sel dari nilai pertama yang tidak kosong.

Ketiga , fungsi indeks hanya mengembalikan nilai pertama yang tidak kosong dalam daftar.

13. Teks yang paling sering

Sekarang anggaplah dengan adanya daftar nilai teks, Anda perlu menghitung teks yang paling sering muncul.

Dalam daftar di bawah ini Anda memiliki nama.

Tapi, ada beberapa nama yang lebih dari satu kali.

Jadi, sekarang Anda perlu mendapatkan nama yang memiliki kemunculan tertinggi dalam daftar. Anda dapat menggunakan rumus dibawah ini yang merupakan gabungan dari MODE, INDEX dan MATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Saat Anda memasukkan rumus ini, ia akan mengembalikan “Tamesha” yang merupakan nama paling umum.

…begitulah cara kerja rumus ini

Pertama , MATCH akan membandingkan seluruh rentang nama dengan dirinya sendiri. Dan, dengan melakukan hal tersebut, ia akan mengembalikan sebuah array yang setiap teksnya akan mewakili posisi pertamanya.

Mari kita ambil contoh nama “Tamesha” yang merupakan nama paling umum dalam daftar. Sekarang jika Anda melihat daftarnya, ini terjadi pertama kali di sel ke-8 dan kemudian di sel ke-12.

Namun jika melihat tabel, untuk semua posisi dimana kita memiliki “Tamesha”, ia mengembalikan 8 yang merupakan posisi pertamanya.

Setelah itu, dari array yang dikembalikan oleh MATCH, fungsi mode akan mengembalikan angka paling sering yaitu nomor sel kemunculan pertama “ Tamesha ”.

Dan pada akhirnya, INDEX akan mengembalikan teks menggunakan nomor sel ini.

14. Buat hyperlink

Sekarang, katakanlah selain mencari suatu nilai, Anda juga ingin membuat hyperlink untuk nilai tersebut. Dengan cara ini Anda dapat dengan cepat menavigasi ke sel tempat kolom pencarian Anda berada.

Misalnya, pada tabel di bawah ini Anda perlu mengetahui usia seseorang. Dan, jika Anda membuat hyperlink untuk nilai ini, Anda dapat dengan mudah menavigasi ke sel tempat nilai ini berada.

Dan untuk ini kita harus menggunakan HYPERLINK + Cell dengan INDEX dan MATCH dan rumusnya menjadi:

Beginilah cara kerja rumus ini

Mari kita pisahkan rumus ini menjadi beberapa bagian agar lebih memahaminya.

• Pertama -tama, Anda menggunakan indeks dan pencocokan dalam fungsi sel. Dan, saat Anda menggunakan kedua fungsi dalam fungsi sel, Anda mendapatkan referensi sel dengan nilai yang sesuai, bukan nilai yang sesuai.
• Kedua , Anda telah menggabungkan “#” dengan referensi sel.
• Ketiga , Anda menggunakan indeks dan pencocokan lagi untuk mendapatkan nilai pencocokan untuk digunakan sebagai teks tautan. Dengan cara ini Anda memiliki nilai yang sesuai serta tautan ke sel tempat nilai tersebut berada.