Partial Lookup 2

Ass. Wr.Wb.

Long time no see you guys, wish you all the best......... ;)

Psotingan kali ini masih melanjutkan tentang Partial Lookup dengan contoh untuk memfilter data sesuai keinginan kita.

Kasus 1:
Misalkan kita punya daftar alamat Restoran beserta alamatnya, seperti ini :

Data tersebut acak dengan alamat resto di Kota Yogyakarta dan Jakarta, bayangkan kalo kita punya database ratusan atau ribuan alamat yang terkadang kita ingin mengambil data tersebut sesuai kebutuhan kita saja. Sesuai contoh diatas, misalnya saja saya ingin menampilan data Resto yang alamatnya di Yogyakarta saja, bisa ndak ya? Pengennya seperti ini hasilnya :

Rumusnya seperti ini
=IF($A$13="";"";IFERROR(INDEX($A$3:$B$10;SMALL(IF(ISNUMBER(SEARCH($A$13;$B$3:$B$10));ROW($A$3:$A$10)-ROW($B$2));ROW(A1:C1));2);""))

di input mulai di Cell B13 dan ci-copas ke bawah


Kasus 2:
Mirip dengan yang diatas hanya saja rumusnya dan data yang diambil berbeda, kasus ke 2 ini kita ingin memfilter Nama Orang.

Dengan Kriteria dan hasilnya yang diinginkan seperti ini:

Rumusnya :
=IFERROR(INDEX(A$26:A$32;SMALL(IF(ISNUMBER(SEARCH($A$35;$A$26:$A$32));ROW($A$26:$A$32)-MIN(ROW($A$26:$A$32))+1);ROWS($1:1)));"")

Versi lainnya:
=IFERROR(INDEX($A$26:$A$32,SMALL(IF(ISERROR(SEARCH($A$35,$A$26:$A$32)),"",MATCH(ROW($A$26:$A$32),ROW($A$26:$A$32))),ROW(A1))),"")

Sumonggo di download File Latihannya

Partial Lookup

Happy Weekend Guys.....

Mohon maaf karena saya baru sempet posting lagi.......... ok apa artinya partial lookup? Misalnya kita punya Kode 007-2BR-121, Nah kode tersebut dipisahkan oleh tanda sambung "-". Masing2x kode tersebut mempresentasikan seperti ini :
007 = Kode untuk Nama Barang
2BR = Kode untuk Nama Supplier
121 = Kode untuk Harga Barang

Jadi masing2x kode memiliki tabel rujukan sehingga ada 3 tabel rujukan....... jadi secara parsial kode tersebut merujuk adanya informasi dari 3 tabel rujukan. Nah psotinga kita kali ini membahas bagaimana sih cara mengambil data dengan partial lookup.

Kasus 1:

Sesuai contoh diatas, kita ingin mengambil data Nama Supplier, Item Barang dan Harga Barang, jadi fungsi LOOKUP kita gabungkan dengan Fungsi Teks seperti LEFT, MID ataupun RIGHT.

Untuk mencari Data Supplier :
=VLOOKUP(LEFT(A3;SEARCH("-";A3)-1);$D$3:$E$5;2;0)

Untuk mencari Data Harga:
=VLOOKUP(MID(A10;SEARCH("-";A10)+1;3)+0;$D$10:$E$12;2;0)

Untuk Mencari Data Item:
=VLOOKUP(RIGHT(A17;4);$D$17:$E$19;2;0)

Kasus 2:

Ini agak sedikit rumit dimana kriteria yang akan kita cari seperti ini Adams.John.EJ04 dimana data yang kita cari ambil hanya EJ04 padahal EJ04 jadi satu kalimat di Adams.John.EJ04

rumusnya :
=VLOOKUP(MID(B11;SEARCH(".";B11;SEARCH(".";B11;1)+1)+1;4);A2:B8;2;0)

Kasus 3:

Lha bagaimana jika Tabel Rujukannya hanya 1 Kolom? Ya itu tambah rumit.... tapi masih bisa diatasi dengan Excel.....

Rumusnya:
=VLOOKUP(MID(A11;FIND(".";A11;FIND(".";A11;1)+1)+1;4)&"*";A2:A8;1;0)

Kasus 4:

Ini mirip dengan kasus 3, hanya saja dengan rumus yang berbeda:
=INDEX(A1:A7;MATCH(LEFT(A10;5);LEFT(A1:A7;5);0))

Mungkin masih ada yang awam mengenai fungsi teks seperti LEFT, MID, RIGHT, SEARCH, FIND, dll mungkin perlu belajar basic-nya terlebih dahulu.....karena mereka fungsi yang cukup sering digunakan untuk manipulasi text string.

Silahkan Download FIle Latihan

Basic Tentang Fungsi IF dengan kombinasi Fungsi OR dan AND

Bismillah

Poostingan hari ini saya ingin menjelaskan al-hal yang mendasar tentang Fungsi IF yang digabung dengan Fungsi OR dan AND. Topik kita kali ini mungkin lebih saya tujukan bagi yang masih pemula atau yang ingin mendalami lebih jauh lagi. Ingatlah kalau kita menguasai yang pelajaran dasar, mengerjakan yang rumit akan dapat diselesaikan.

IF dan OR :

Sintaksnya : =IF(OR(Kondisi1;Kondisi2);X;Y)
X = Nilai Jika Salah Satu Kondisi Terpenuhi (Ingat OR, minimal salah satu kondisi terpenuhi)
Y= Nilai Jika Semua Kondisi Tidak Terpenuhi

Mari kita praktek............. saya akan pakai contoh yang sederhana saja.......

Kita punya Tabel dimana Cell A1=1 dan B1=2 dan C1=3
Kita tes dengan rumus :
=IF(OR(A1=1;B1=2;C1=3);"Ya";"Tidak")
Bunyinya begini, "Jika A1=1 atau B1=2 atau C1=3, maka jawab "Ya"; jika tidak ada maka jawab "Tidak"
Cobalah ganti angka2x diatas dengan angka yang lain untuk men-tes rumus anda. Rumus ini akan menmunculkan "Ya" selama salah satu kondisi terpenuhi, yaitu A1=1 atau B1=2 atau C1=3, sebaliknya akan muncul "Tidak" jika semua kondisi tadi tidak terpenuhi.

Anda mungkin ada yang belum tahu, adan variasi rumus yang lain yang hasilnya sama dengan kombinasi IF dan OR, contohnya dibawah ini :
=IF((A1=1)+(B1=2)+(C1=3);"Ya";"Tidak")

atau
=IF(A1=1;"Ya";IF(B1=2;"Ya";IF(C1=3;"Ya";"Tidak")))

atau
=IF(OR(A1=1;B1=2);"Ya";IF(C1=3;"Ya";"Tidak"))

atau
=IF(A1=1;"Ya";IF(OR(B1=2;C1=3);"Ya";"Tidak"))

Cobalah utak atik dengan rumus diatas untuk menguji konsistensi rumus tersebut, bisa saja saya salah hehe....

Mungkin ada rekan yang mau share rumusnya? dipersilahkan.....


IF dan AND

Sekarang mari membahas topik yang kedua IF dan AND, saya akan pake kasus diatas dimana kondisinya jika A1=1 dan B1=2 dan C1=3 maka jawablah "Ya", jika salah satu kondisi tidak terpenuhi jawablah "Tidak". Jadi kebalikan dari rumus IF dan OR diatas. Gabungan IF dan AND mensyaratkan semua kondisi harus terpenuhi untuk memunculkan jawaban "Ya".

rumus yang reguler seperti ini:
=IF(AND(A1=1;B1=2;C1=3);"Ya";"Tidak")

variasinya:
=IF((A1=1)*(B1=2)*(C1=3);"Ya";"Tidak")

atau
=IF(A1=1;IF(B1=2;IF(C1=3;"Ya";"Tidak");"Tidak");"Tidak")

Semuanya hasilnya sama yaitu merupakan gabungan Fungsi IF dan Fungsi AND atau kondisi AND.
Dan jangan lupa untuk utak-atik ya, supaya rumus seperti ini bisa dikembangkan dan dipakai untuk memecahkan persoalan yang memerlukan kalkulasi dengan Excel

Download File Latihan

Regards

Menghitung Rentang Tanggal dan Rentang Angka Desimal

Good to see u all guys...... langsung ke TKP ya, kita punya tabel kasus seperti dibawah ini

Kita kepengen menghitung berapa data yang muncul sesuai kriteria tanggal dan ang desimal diatas, dan data tersebut diambil dari 2 Tabel yang terpisah.

Well, ada beberapa cara penyelesaiannya :
1. =SUMPRODUCT((A1:A7>="20/9/2012"*1)*(A1:A7<="1/9/2013"*1))+SUMPRODUCT((C1:C7>="3,00"*1)*(C1:C7<="3,50"*1))

2. =COUNTIF(A1:A7;">=20/9/2012")-COUNTIF(A1:A7;">=1/9/2013")+COUNTIF(C1:C7;">=3,00")-COUNTIF(C1:C7;">=3,50")

3. =SUMPRODUCT( --(A1:A7>=DATEVALUE("20/9/2012")); --(A1:A7<=DATEVALUE("1/9/2013")))+SUMPRODUCT((C1:C7>="3,00"+0)*(C1:C7<="3,50"+0))

hasilnya adalah 6

Untuk belajar lebih mudah, silahkan di-download file latihannya....

Mengambil Data Penjualan Terbesar

Misalkan kita punya sebuah perusahaan yang punya anak buah di bagian Penjualan/Sales, dan pengen ngasih perngahargaan bagi Sales Person yang berhasil menjual barang terbanyak.

KASUS 1:

Results yang diinginkan:

Untuk Kolom Sales (Qty)
=IFERROR(MAX(IF($A$2:$A$9=A14;$C$2:$C$9;""));"")

Untuk Kolom Sales Person
=IFERROR(IF(B14="";"";IF(INDEX($A$2:$C$9;SMALL(IF($C$2:$C$9=B14;ROW($A$2:$A$9)-ROW($B$1));COUNTIF(B$14:B14;B14));1)=$A$14;INDEX($A$2:$C$9;SMALL(IF($C$2:$C$9=B14;ROW($A$2:$A$9)-ROW($B$1));COUNTIF(B$14:B14;B14));2)));"")

Semuanya Array Formula

KASUS 2:
Misalkan lagi punya Tabel seperti ini

Dan pengennya diurutkan data penjualannya menjadi seperti ini (Tertinggi ke Terendah):

Rumusnya:
=INDEX($A$2:$A$7;MATCH(LARGE(($B$2:$B$7+ROW($B$2:$B$7)/10000);ROWS($B$2:$B2));($B$2:$B$7+ROW($B$2:$B$7)/10000); 0))

Array Formula lagi tuh....

Biar gak bingung, monggo dipun download file latihannya

Pengambilan Data Terakhir Secara Horizontal

Postingan kali ini mirip dengan artikel saya tentang pengambilan data terakhir juga, hanya berbeda bentuk tabel kasus dan penyelesaiannya.

Tabel Kasus 1:

Kita pengennya ambil data penjualan terakhir dari masing2x Sales Person, seperti ini :

Bagaimana rumus yang bisa diaplikasikan?
1.=INDEX(C2:G2;1;COUNT(C2:G2))
2. =OFFSET(B10;;COUNT(C10:G10))
3. =LOOKUP(9,99999999999999E+307;C18:G18)
4. =INDEX(C26:G26;MATCH(9,99999999999999E+307;C26:G26))
5. =INDEX(C26:G26;1;MAX((C26:G26<>"")*COLUMN(C26:G26))-COLUMN(C26:G26)+1)


Tabel Kasus 2 (Dengan Kriteria):

Rumus untuk mencari data sales terakhir dengan kriteria "Adi" dan "Sleman" adalah
=LOOKUP(2;1/(INDEX($C$42:$G$45;MATCH(B47&B48;$A$42:$A$45&$B$42:$B$45;0);)<>"");INDEX($C$42:$G$45;MATCH(B47&B48;$A$42:$A$45&$B$42:$B$45;0);))

Download File Latihan

Fungsi SUBSTITUTE Bercabang (Nested Substitute)

Pernah menggunakan Fungsi Substitute? yang berguna untuk mengganti teks yang lama dengan yang baru, dengan sintaks =SUBSTITUTE(text;old_text;new_text). Contoh sederhana misal pada cell A1 tertulis teks "Agus", kemudian kita ingin menggantinya dengan "Dewi", rumusnya =SUBSTITUTE(A1;"Agus";"Dewi")
Mudah bukan?

Tapi bagaimana dengan teks yang ingin kita ganti lebih dari satu? 2 atau lebih? Fungsi SUBSTITUTE ini mengijinkan kita untuk dibuat nested atau bercabang seperti halnya Fungsi IF bercabang (Nested IF's).

Kasus: Kita ingin merapikan data yang kondisinya seperti ini:

Yang ingin dihilangkan adalah karakter tanda sambung(-), titik(.), koma(,) dan garis miring(/) dan diganti dengan karakter kosong atau "" kalau di MS Excel.

Bagaimana Nested Substitute itu?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"-";"");",";"");".";"");"/";"")

Silahkan jika ingin ada penambahan karakter yang ingin diganti/dihilangkan dengan karakter yang baru.

Salam

Download File Latihan (Excel 2003)
Download File Latihan (Excel 2010)

Menghitung Bonus/Komisi Penjualan

Sebenarnya ini sama dengan postingan saya mengenai pengambilan data dari 2 tabel, saya akan memberikan contoh pada kasus menghitung bonus atau komisi berdasarkan hasil penjualan salesman.

Tabel Kasus 1:

Pada kolom bonus yang saya warna kuning akan muncul secara otomatis yang merupakan bonus (%) berdasarkan kriteria Sales/Penjualan dan Masa Kerja yang dibedakan menjadi 2, yaitu :
1. Masa Kerja <=5 Tahun
2. Masa Kerja > 5 Tahun

Rumus yang bisa diterapkan untuk mencari Bonus (%) dari 2 tabel referensi berdasarkan masa kerja karyawan/salesman:

1. =VLOOKUP(C2;IF(B2<=5;$G$2:$H$6;$J$2:$K$6);2)

2. =IF(B2<=5;VLOOKUP(C2;$G$2:$H$6;2);VLOOKUP(C2;$J$2:$K$6;2))

3. =IF(B2<=5;INDEX($G$2:$H$6;MATCH(C2;$G$2:$G$6);2);INDEX($J$2:$K$6;MATCH(C2;$J$2:$J$6);2))


Tabel Kasus 2:
Contoh yang kedua ini sebenarnya hampir sama, tapi berbeda bentuk tabel dan penyelesaiannya,

Kemudian untuk tabel rujukan discount

Mohon diperhatikan ada 3 tabel diatas, Tabel Pro1 dan Pro2 sudah jelas adalah tabel potongan harga atau discount. Tabel yang ke-3 adalah tabel rujukan/referensi untuk menentukan posisi tabel discount bagi Pro1 dan Pro2.

Pada Cell C17 di-input =VLOOKUP(A17;$G$24:$H$25;2) yang akan menghasilkan nilai 1 sesuai Tabel Rujukan diatas, kemudian copas kebawah ke Cell C18.

Pada Cell D17 di-input
=B17*INDEX(($H$17:$H$21;$K$17:$K$21);MATCH(B17;$G$17:$G$21);;C17)
yang akan menghasilkan nilai Rp.5000, kemudian copas-lah ke bawah sampai Cell D18.

Pada Cell E17 di-input =INDEX(($H$17:$H$21;$K$17:$K$21);MATCH(B17;$G$17:$G$21);;C17) yang akan menghasilkan nilai 2%

Alternative rumus Cell D17 (Disc Rp.) :
=B17*VLOOKUP(B17;CHOOSE(C17;G17:H21;J17:K21);2)

=B17*INDEX((H17:H21;K17:K21);MATCH(B17;G17:G21);;VLOOKUP(A17;G24:H25;2;FALSE))

Alternative rumus Cell E17 (Disc %) :
=VLOOKUP(B17;CHOOSE(C17;G17:H21;J17:K21);2)

=INDEX((H17:H21;K17:K21);MATCH(B17;G17:G21);;VLOOKUP(A17;G24:H25;2;FALSE))

Download File Latihan

Ambil Data dengan "OR" Criteria

Mungkin pembaca sudah familiar dengan penggunaan Fungsi OR yang digabung dengan Fungsi IF, seperti ini: =IF(OR(A1=2;B1=3);"Ya";"Tidak"), jika salah satu kondisi yaitu A1=2 atau B1=3 terpenuhi maka akan ditampilkan Teks "Ya", sebaliknya jika keduanya tidak terpenihi maka akan ditampilkan "Tidak". Ini contoh yang sederhana yang bayak digunakan oleh para pengguna Excel. Saya akan memberikan contoh pengambilan data dengan kondisi OR tanpa menggunakan Fungsi OR, bingung ya...langsung aja ke TKP.

Tabel Kasus:

Dengan kriteria dan hasil yang diinginkan:

Jadi ada 2 kriteria, yaitu Area atau Nama, yang berdasarkan salah satu dari 2 kriteria tsb, kita ingin mengambil data Item atau Produk.

Well ini contoh2x rumusnya:

dan ada lagi....

Silahkan dicoba kasus dan rumus diatas, dan siapa tahu anda bisa membuat rumus dan berkreasi sendiri.

Download File Latihan (Excel 2003)
Download File Latihan (Excel 2010)

Mengurutkan Data dan Menghilangkan Duplikasi

Postingan kali ini saya ingin membahas topik mengurutkan data dan menghilangkan data yang dobel. Mungkin ada kasus yang tabel data yang datanya acak / random dan kita ingin merapikannya. Saya kasih contoh tabel2x yang sederhana supaya lebih mudah memahami rumusnya.

Tabel Kasus 1:

Pada gambar diatas hasil yang dicapai adalah mengambil data unik dan yang ke-2 sekaligus mengurutkannya.

Rumus Data Unik (Extract Unique Values)
1. =IFERROR(INDEX($A$3:$A$11; MATCH(0;COUNTIF($C$2:C2;$A$3:$A$11);0));"")
Rumus Data nik dan Sort
2. =IFERROR(INDEX($A$3:$A$11;MATCH(0;COUNTIF($A$3:$A$11;"<"&$A$3:$A$11)-SUM(COUNTIF($A$3:$A$11;"="&E$2:E2));0));"")

Tabel Kasus 2:

Kasus yang ke-2 terdapat blank atau baris yang kosong, jika menggunakan rumus pada kasus sebelumnya tidak akan berhasil.
 Rumus Data Unik
1. =IFERROR(INDEX($A$15:$A$23; MATCH(0;IF(ISBLANK($A$15:$A$23);1;COUNTIF($C$14:C14; $A$15:$A$23)); 0));"")
2. =IFERROR(INDEX($A$15:$A$23;AGGREGATE(15;6;IF(FREQUENCY(IFERROR(MATCH(IF($A$15:$A$23<>"";$A$15:$A$23);$A$15:$A$23;0);"");ROW($A$15:$A$23)-ROW($A$15)+1);ROW($A$15:$A$23)-ROW($A$15)+1);ROWS($A$1:A1)));"")

Rumus Data Unik dan Sort
3. =IFERROR(INDEX($A$15:$A$23;MATCH(SMALL(IF($A$15:$A$23<>"";IF(ISNA(MATCH($A$15:$A$23;$F$14:F14;0));MMULT((IF($A$15:$A$23<>"";$A$15:$A$23)>TRANSPOSE(IF($A$15:$A$23<>"";$A$15:$A$23)))+0;ROW($A$15:$A$23)^0)));1);MMULT((IF($A$15:$A$23<>"";$A$15:$A$23)>TRANSPOSE(IF($A$15:$A$23<>"";$A$15:$A$23)))+0;ROW($A$15:$A$23)^0);0));"")

DOWNLOAD FILE LATIHAN

Menjumlahkan dari 2 Tabel Berbeda Dimensi

Pernahkah anda mempunyai kasus punya 2 tabel yang berbeda dimensi, Tabel A mempunyai 5 baris data dan Tabel B mempunyai 10 baris data, dan kita harus menjumlahkan item dari 2 tabel tersebut? Mari kita membahasnya.

Tabel Kasus:

Hasil yang diinginkan menjumlahkan........

Rumus yang bisa diaplikasikan (Input di cell B13 dan copy ke bawah) :
=SUMIF($A$2:$D$9;A13;$B$2:$E$9)

atau

=SUMIF($A$2:$B$6;A13;$B$2:$B$6)+SUMIF($D$2:$E$9;A13;$E$2:$E$9)

atau

=IFERROR(VLOOKUP(A13;$A$2:$B$6;2;FALSE);0)+VLOOKUP(A13;$D$2:$E$9;2;FALSE)

Salam

Download File Latihan

Membuat Akhiran Untuk Angka dan Tanggal

Pernah membaca tulisan angka seperti ini? 1st, 2nd atau 3rd atau di bahasakan menjadi yang pertama, kedua dan ketiga. Dalam bahasa inggris 1st, 2nd dan 3rd adalah Ordinal Suffixes atau akhiran yang menandakan suatu urutan. Misal "Anna seated in 1st (dibaca "first") class" atau "Anna is the 2nd (dibaca "second") born". Nah saya ingin membahas tentang ini, dengan Excell tentunya. Bagaimana menambahkan akhiran urutan ini pada sebuah angka dan tanggal.

Hasil yang diinginkan seperti ini:

Rumus yang bisa diterapkan seperti ini:

=A2&""&MID("thstndrdthstndrdth";MATCH(IF(MOD(A2;100)>29;MOD(A2;10)+20;MOD(A2;100));{0;1;2;3;4;21;22;23;24};1)*2-1;2)

atau

=A2&MID("thstndrdth";MIN(9;2*RIGHT(A2)*(MOD(A2-11;100)>2)

atau

=A2&IF(OR(MOD(A2;100)={11\12\13});"th";LOOKUP(--RIGHT(A2);{0\"th";1\"st";2\"nd";3\"rd";4\"th"}))

atau

=A28&IF(AND(A2>=10;A2<=14);"th";CHOOSE(MOD(A2;10)+1;"th";"st";"nd";"rd";"th";"th";"th";"th";"th";"th"))

Contoh berikut adalah penambahan akhiran untuk Tanggal:

Rumus yang dapat diaplikasikan:

=DAY(A1)&IF(OR(DAY(A1)={1;2;3;21;22;23;31});CHOOSE(1*RIGHT(DAY(A1)*0,1);"st";"nd";"rd ");"th")&TEXT(A1;" mmmm, yyyy")

atau

=DAY(A3)&IF(INT(MOD(DAY(A3);100)/10)=1;"th"; IF(MOD(DAY(A3);10)=1;"st";IF(MOD(DAY(A3);10)=2;"nd";IF(MOD(DAY(A3);10)=3;"rd";"th"))))& " day of " & TEXT(A3;"mmmm yyyy")

atau

= TEXT(A5;"mmmm d")&LOOKUP(DAY(A5);{1\2\3\4\21\22\23\24\31;"st"\"nd"\"rd"\"th"\"st"\"nd"\"rd"\"th"\"st"})&", "&YEAR(TODAY())

atau

=TEXT(A7;"mmmm") & " " & DAY(A7) &MID("thstndrdth";MIN(9;2*RIGHT(DAY(A7))*(MOD(DAY(A7)-11;100)>2)+1);2) & ", " & YEAR(A7)

Download File Latihan

Menghitung dengan Multi Kriteria

Postingan saya kali ini ingin mengupas penggunaan rumus Excel untuk menghitung dengan multi kriteria.

Tabel Kasus

Kriteria yang diterapkan:
1. Tempe
2. >= 1 Januari 2013
3. Samidi

Rumus yang bisa digunakan:

Mengambil Data dengan Multi Kriteria

Langsung saja ke TKP

Tabel Kasus:

4 Kriteria :

Dan kita ingin mengambil data pada kolom ke 5 (Kolom Sales).

Rumus 1:
=SUMIFS(E2:E11;A2:A11;B14;B2:B11;B15;C2:C11;C14;D2:D11;C15)

Rumus 2:
=SUMPRODUCT(E2:E11;(B2:B11=B15)*(A2:A11=B14)*(C2:C11=C14)*(D2:D11=C15))

Rumus 3: Rumus Array
=INDEX(E2:E11;MATCH(B14&B15&C14&C15;A2:A11&B2:B11&C2:C11&D2:D11;0))

Rumus 4: Rumus Array
=VLOOKUP(B14;IF((B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15);A2:E11;"");5;FALSE)

Rumus 5: Rumus Array
=SUM(E2:E11*(B2:B11=B15)*(A2:A11=B14)*(C2:C11=C14)*(D2:D11=C15))

Rumus 6: Rumus Array
=VLOOKUP(B14&"/"&B15&"/"&C14&"/"&C15;CHOOSE({1\2\3\4};A2:A11&"/"&B2:B11&"/"&C2:C11&"/"&D2:D11;E2:E11);2;FALSE)

Rumus 7: Rumus Array
=INDEX(E2:E11;MATCH(1;(A2:A11=B14)*(B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15);0))

Rumus 8: Rumus Array
=INDEX(E2:E11;SUMPRODUCT(--(A2:A11=B14)*(B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15)))

Rumus 9: Rumus Array
=INDEX(E2:E11;MATCH(1;IF(A2:A11=B14;IF(B2:B11=B15;IF(C2:C11=C14;IF(D2:D11=C15;1));0))))

Rumus 10: Rumus Array
=IFERROR(INDEX(E:E;MATCH(1;(A:A=B14)*(B:B=B15)*(C:C=C14)*(D:D=C15);0));0)

Rumus 11: Rumus Array
=INDEX(A2:E11;MATCH(B14&B15&C14&C15;A2:A11&B2:B11&C2:C11&D2:D11;0);5)

dan mungkin masih ada variasi rumus yang lain dan belum ditemukan...... silahkan anda coba2x

Download File Latihan

Mencari Data Dengan Kriteria Rentang Tanggal (Date Ranges)

Pernahkah anda mempunyai kasus seperti ini, tabel kita merupakan tabel data jadual kegiatan yang mencantumkan rentang tanggal yang berisikan informasi ataupun kegiatan. Lebih jelasnya cobalah tengok tabel dibawah ini.

Tabel Kasus 1:

Hasil yag kita inginkan adalah informasi atau kegiatan yang tercakup di dalam rentang tanggal yang ada di Tabel Data. Pada contoh diatas, kita input pada cell C8 adalah tanggal 7 Desember 2013, dan tanggal tersebut terletak pada Rentang Tanggal "Trimester 4".

Rumusnya (Array Formula) :

Tabel Kasus 2:

Contoh kedua ini hampir sama dengan contoh 1, hanya beda format penulisan rentang tanggalnya.

Rumusnya (Array Formula):

Tabel Kasus 3: (Date Ranges terpisah di 2 Kolom

Rumusnya (Array Formula):

Tabel Kasus 4: (2 Kegiatan Berbeda di Satu Rentang Tanggal)

Rumusnya (Array Formula):

Tabel Kasus 5:

Rumusnya (Array Formula):

Tabel Kasus 6:

Hasil yang dinginkan:

Download File Latihan