Kasus Tabel Dengan Lay Out Yang Sulit

Kebetulan saya ada beberapa kasus menarik yang menyangkut Lookup Data tetapi dengan bentuk tabel yang agak sulit dengan rumus yang reguler. Maksud saya rumus reguler adalah rumus yang lazim digunakan atau yang sudah digunakan secara luas.

Seperti ini misalnya:

Kasus 1:

Nah mungkin pernah liat yang beginian? Terus misal kriterianya adalah Buah dan kita ingin mengambil hasilnya dari Kolom B, itu berarti "Apel", "Jeruk" dan "Durian"

Hasil yang diinginkan adalah seperti yang diatas, kriteria yang dipakai adalah Kopi dan Apel, nah coba memakai rumus VLOOKUP atau INDEX + MATCH pasti gagal deh... huehehe....

Rumus 1 (Kriteria "Kopi") :
=IFERROR(INDEX($B$2:$B$10;SMALL(IF(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10)=$A$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")

Rumus 2 (Kriteria "Apel") :
=IFERROR(INDEX(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10);SMALL(IF($B$2:$B$10=$D$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")

Kasus 2:

Kemudian hasil yang diinginkan adalah mengambil data QTY atau jumlah barang yang terjual oleh kriteria "Nama Sales", "Jenis Barang" dan "Merek Barang"

Rumusnya:
=INDEX(B22:E25;MATCH(1;(INDEX(B22:D25;0;MATCH($A28;B21:D21;0))=B29)*(A22:A25=A29);0);MATCH($A28;B21:D21;0)+1)

Kasus 3:

Contoh diatas ada juga Column Header yang Cell-nya digabung atau Merged, sebetulnya untuk penggunaan rumus pada Cell yang di-merged tidak disarankan biasanya akan gagal waktu di-ENTER, atau kalau jika digunakan sebagai kriteria agak ribet rumusnya, sebisa mungkin tabel data bersih yaitu tidak ada Cell yang di-MERGED dan tidak ada karakter spasi kosong pada tabel anda.

Sesuai Tabel Kasus 3 diatas, hasil yang diinginkan seperti ini

Nah kriterianya adalah "Area" kemudian hasil yang diinginkan adalah semua baris data yang ada dibawahnya...

Rumus Untuk Tanggal :
=IFERROR(INDEX($B$38:$H$38;SMALL(IF(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44;COLUMN($B$37:$H$37)-COLUMN($B$37)+1);COLUMNS($A20:A20)));"")

Rumus Untuk Qty:
=IFERROR(INDEX($B$39:$H$41;SMALL(IF((INDEX($B$39:$H$41;0;MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0))<>"");MATCH(ROW($A$39:$A$41);ROW($A$39:$A$41));"");ROWS($A$20:$A20));MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0));"-")

Semuanya adalah Rumus Array, tekan tombol CTRL-SHIFT-ENTER bersamaan

Salam

File Latihan