Lookup Dari Multiple Sheets

Met malem readers, kadang-kadang saya dapat ide untuk artikel blog ini dari pembaca yang menanyakan kasusnya, nah kali ini artikel ini ide dari Bapak Dady Lucky, terima kasih pak sudah memberikan inspirasi malam ini dan kebetulan saya belum pernah posting artikel seperti ini. Sesuai judul artikel bagaimana sih melakukan Lookup dari beberapa Sheets? Langsung ya ke tkp.....................

1. Mengambil hasil dari beberapa tab sheets (One Results)

Dan kunci keberhasilan lookup dari beberapa tabel dari sheet yang berbeda, anda harus punya tabel dengan lay out yang sama walaupun datanya beda2x, jika tidak tidak akan berhasil. Dan saya punya beberapa tab sheets disini

Ada 5 sheets ya dan kemudian hasil yang diinginkan

Dan rumusya adalah, letakkan pada sheet "Hasil" pada cell B5 kemudian copas ke bawah dan ke samping:
=IFERROR(VLOOKUP($B$2;INDIRECT("'"&INDEX({1\2\3\4\5};AGGREGATE(15;6;1/(1/{1\2\3\4\5}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5}&"'!A2:A100");$B$2)=1));ROW(A1)))&"'!$A$2:$A$100");1;0);"")


2. Mengambil hasil dari beberapa tab sheets (Multiple Results)

Dan saya punya 3 sheet saja disini

Hasil yang diharapkan

Nah rumusnya (rumus array) :
=IF(ROWS($1:1)>SUMPRODUCT(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1));"";INDEX(INDIRECT("'"&INDEX({"A"\"B"\"C"};MATCH(TRUE;MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1)))>=ROWS($1:1);0))&"'!A2:B10");SMALL(IF(INDIRECT("'"&INDEX({"A"\"B"\"C"};MATCH(TRUE;MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1)))>=ROWS($1:1);0))&"'!B2:B10")=$B$1;ROW(INDIRECT("1:"&ROWS($A$2:$B$10))));IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1);1+MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1))));ROWS($1:1)));COLUMNS($A:A)))

Have good night and sleep tight readers......:)

Download :
File_1
File_2

Kumpulan Kasus Excel Jilid 6

Kumpulan Kasus back to fight guys, malam ini melanjutkan seri yang ke 6 dan mudah2xan dapat digunakan untuk keperluan para readers, langsung saja ke urutan pertama

KASUS 1

Kasus pertama ini adalah menggabungkan kolom, 2 kolom atau lebih ya

Rumus:
a. =IFERROR(IF(OFFSET(A$2;MOD(ROWS(A$2:A2)-1;COUNTA(A:A)-1);INT((ROWS(A$2:A2)-1)/ROWS($A$2:$B$10)))=0;"";OFFSET(A$2;MOD(ROWS(A$2:A2)-1;COUNTA(A:A)-1);INT((ROWS(A$2:A2)-1)/ROWS($A$2:$B$10))));"")

b. =IFERROR(INDEX($A$2:$B$10;MOD(ROWS($A$2:A2)-1;ROWS($A$2:$B$10))+1;INT((ROWS($A$2:A2)-1)/ROWS($A$2:$B$10))+1);"")

KASUS 2

Kasus ke 2 ini adalah kasus penjumlahan dengan kolom kriteria yang di-merged, hasilnya

Rumus:
a. =SUM(MMULT(N(LOOKUP(ROW($A$1:$A$49);IF($A$1:$A$49<>"";ROW($A$1:$A$49)))=TRANSPOSE(IF($A$1:$A$49=D4;ROW($A$1:$A$49))));ROW($A$1:$A$49)^0)*$B$1:$B$49)

b. =SUM(IFERROR(IF(MATCH(LOOKUP(ROW(D);IF(D<>"";ROW(D)));IF(D=D4;ROW(D));0);V);0)) --> array formula

c. =SUM(ISNUMBER(MATCH(LOOKUP(ROW(D);IF(D<>"";ROW(D)));IF(D=D4;ROW(D));0))*V) --> array formula

d. =SUM(IF(LOOKUP(ROW(D);ROW(D)/(D<>"");D)=D4;V))

KASUS 3

Kasus ini merupakan kasus lookup dengan beberapa kolom, hasilnya

Rumus:
=IFERROR(SUMIF(INDEX($B$2:$F$8;MATCH(B10;$A$2:$A$8;0);0);A10;INDEX($C$2:$G$8;MATCH(B10;$A$2:$A$8;0);0));"")

KASUS 4

Kasus ke 4 ini adalah inspirasi dari mas Hendra jadi saya share untuk artikel kali ini tentang Golongan dan Kepangkatan PNS, untuk hasilnya adalah yang diwarna kuning ya, dan ini merupakan tabel panduannya

Rumusnya
=IFERROR(IF(RIGHT($D3;1)="E";$D3;IF(AND(LOOKUP(LEFT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));FIND("/";INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)-1);{"I";"II";"III";"IV"};{1;2;3;4})=LOOKUP(LEFT(D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});RIGHT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)<RIGHT($D3;1));$D3;IF(AND(LOOKUP(LEFT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));FIND("/";INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)-1);{"I";"II";"III";"IV"};{1;2;3;4})=LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});RIGHT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)=RIGHT($D3;1));$D3;IF(RIGHT($D3;1)="D";LOOKUP(LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4})+1;{1;2;3;4};{"I";"II";"III";"IV"})&"/"&"A";LOOKUP(LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});{1;2;3;4};{"I";"II";"III";"IV"})&"/"&CHAR(CODE(RIGHT($D3;1))+1)))));"")

KASUS 5

Hasil yang diharapkan

Rumus:
a. =INDEX(AGGREGATE({14;15};6;$B$2:$E$5/(($G2=$A$2:$A$5)*ISNUMBER($B$2:$E$5));1);COLUMNS(A:$A))

b. =IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$5;MATCH($G2;$A$2:$A$5;0);0)=$H2;COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COUNTIFS($G$2:G2;G2;$H$2:H2;H2)));"")

KASUS 6

Kasus terakhir ini merupakan kasus split teks dengan kriteria, pada tabel diatas saya berikan contoh menggunakan angka supaya hasilnya lebih jelas walaupun jika diganti teks bisa saja, hasil

Rumus di cell A8:
=LEFT($A$1;FIND("@";SUBSTITUTE($A$1;" ";"@";$A$6))-1)

Rumus di cell A9 dan copas ke bawah
=IFERROR(MID($A$1;FIND("@";SUBSTITUTE($A$1;" ";"@";$A$6*ROWS($1:1)))+1;MMULT(FIND("@";SUBSTITUTE($A$1;" ";"@";TRANSPOSE(MMULT(ROW($A1:$A2);1))*$A$6));{-1;1})-1);"")


FILE LATIHAN EXCEL