Formula umum
=INDEX(data,MATCH(lookup,ids,0),2)
Ringkasan
Untuk menggabungkan atau menggabungkan tabel yang memiliki id yang sama, Anda dapat menggunakan fungsi INDEX dan MATCH. Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
=INDEX(data,MATCH($C5,ids,0),2)
di mana "data" adalah rentang bernama H5: J8 dan "id" adalah rentang bernama H5: H8.
Penjelasan
Formula ini menarik nama dan negara bagian pelanggan dari tabel pelanggan ke dalam tabel pesanan. Fungsi MATCH digunakan untuk menemukan pelanggan yang tepat dan fungsi INDEX digunakan untuk mengambil data.
Mengambil nama pelanggan
Bekerja dari dalam ke luar, fungsi MATCH digunakan untuk mendapatkan nomor baris seperti ini:
MATCH($C5,ids,0)
- Nilai pencarian berasal dari id pelanggan di C5, yang merupakan referensi campuran, dengan kolom terkunci, sehingga rumus dapat dengan mudah disalin.
- Array pencarian adalah id rentang bernama (H5: H8), kolom pertama di tabel pelanggan.
- Jenis pencocokan disetel ke nol untuk memaksa pencocokan tepat.
Fungsi MATCH mengembalikan 2 dalam kasus ini, yang masuk ke INDEX sebagai nomor baris:
=INDEX(data,2,2)
Dengan nomor kolom yang di-hardcode sebagai 2 (nama pelanggan ada di kolom 2) dan array diatur ke rentang bernama "data" (H5: J8) INDEX mengembalikan: Amy Chang.
Mengambil status pelanggan
Rumus untuk mengambil status pelanggan hampir identik. Satu-satunya perbedaan adalah nomor kolom di-hardcode sebagai 3, karena info status muncul di kolom ke-3:
=INDEX(data,MATCH($C5,ids,0),2) // get name =INDEX(data,MATCH($C5,ids,0),3) // get state
Pertandingan dua arah yang dinamis
Dengan menambahkan fungsi MATCH lainnya ke rumus, Anda dapat menyiapkan pencocokan dua arah yang dinamis. Misalnya, dengan rentang bernama "header" untuk H4: J4, Anda dapat menggunakan rumus seperti ini:
=INDEX(data,MATCH($C5,ids,0),MATCH(E$4,headers,0))
Di sini, fungsi MATCH kedua telah ditambahkan untuk mendapatkan nomor kolom yang benar. MATCH menggunakan tajuk kolom saat ini di tabel pertama untuk menemukan nomor kolom yang benar di tabel kedua, dan secara otomatis mengembalikan nomor ini ke INDEX.
