Glad to be back, mohon maaf agak lama tidak posting karena saya sakit flu, maklum lagi musim pancaroba, penyakit berjangkit dimana-mana.
Yang terpenting hari ini ada postingan baru yak... kali ini membahas 2 Way Lookup lagi ya tetapi berbeda dengan yang pertama , tulisan terdahulu hasilnya hanya satu saja (single result), nah kenapa tidak dengan hasil yang beberapa atau multi results. Langsung saja tabel kasus dibawah ini :
Seperti kita paham 2 way lookup mempunyai bentuk tabel seperti diatas terdapat Column Header (Mata Pelajaran) dan Row Header (Nama). Nah Pertanyaannya, mungkin ndak ya jika kita ingin mengambil data berdasar Nilainya? Misal:
Dari kriteria "Nilai", hasil yang diinginkan adalah Nama2x yang mendapatkan nilai tersebut, sekalian dengan Mata Pelajaran yang diambil.
Rumus Nama:
=IFERROR(INDEX($A$2:$A$6;SMALL(IF(($B$2:$E$6=$A$9)*($B$2:$E$6<>"");ROW($B$2:$E$6)-ROW($B$2)+1);ROWS(A$1:A1)));"-")
Rumus Mata Pelajaran:
=IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$6;MATCH($B9;$A$2:$A$6;0);0)=$A$9;COLUMN($B$2:$E$6)-COLUMN($B$2)+1);COUNTIF($B$9:B9;B9)));"-")
Pertanyaan 2:
Kriterianya adalah Nama, dan ingin mengambil data Nilai dan Mata Pelajarannya.
Rumus Nilai:
=IFERROR(INDEX($B$2:$E$6;SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6));"");ROW(A1));1*(SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6))+1*MATCH(COLUMN($B$2:$E$6);COLUMN($B$2:$E$6));"");ROW(A1))-SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6));"");ROW(A1))));"-")
Rumus Mata Pelajaran:
=IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$6;MATCH(A$18;$A$2:$A$6;0);0)=$B18;COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COUNTIFS($A$18:$A$18;$A$18;$B$18:B18;B18)));"-")
Pertanyaan 3:
Kriterianya adalah Mata Pelajaran, hasil yang diambil adalah data Nilai dan Nama Siswa.
Rumus Nilai:
=IFERROR(INDEX($B$2:$E$6;SMALL(IF((INDEX($B$2:$E$6;0;MATCH($A$25;$B$1:$E$1;0))<>"");MATCH(ROW($A$2:$A$6);ROW($A$2:$A$6));"");ROW(A1));MATCH($A$25;$B$1:$E$1;0));"-")
Rumus Nama Siswa:
=IFERROR(INDEX($A$1:$A$6;SMALL(IF($B$1:$E$1=$A$25;IF(ISNUMBER(MATCH($B$2:$E$6;$B$25:$B$29;0));IF(ISNA(MATCH($A$2:$A$6;$B2:E$6;0));ROW($A$2:$A$6)-ROW($A$1)+1)));ROWS($A$1:A1)));"-")
Semuanya array formulas ya....dan copy kebawah sampai hasilnya blank.
Download File Latihan
