[belajar-excel] Mengambil Data dengan Multiple Kriteria [1 Attachment]

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Mengambil Data dengan Multiple Kriteria [1 Attachment]

Milis Belajar Excel mailing list
Assalamualaikum, salam sejahtera dan selamat siang para Master dan member milis,
Saya punya kendala dalam pengambilan data dengan menggunakan multiple kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS, SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin bantuan master dan member sekalian. Terima kasih dan sukses selalu.


Sent from Yahoo Mail on Android

contoh soal (13K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengambil Data dengan Multiple Kriteria

Milis Belajar Excel mailing list
Hai Febry,Coba dilampirkan dummy file Excelnya dan apa yang sdh dilakukan.
Salam,HK
On Friday, July 21, 2017, 5:17:38 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:

   
Assalamualaikum, salam sejahtera dan selamat siang para Master dan member milis,

Saya punya kendala dalam pengambilan data dengan menggunakan multiple kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS, SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin bantuan master dan member sekalian. Terima kasih dan sukses selalu.


Sent from Yahoo Mail on Android  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengambil Data dengan Multiple Kriteria [1 Attachment]

Milis Belajar Excel mailing list
File nya sudah saya lampirkan pak Hendrik. Mohon bimbingannya.

Best Regards,FERY


On Friday, July 21, 2017, 5:33:20 PM GMT+7, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> wrote:

 

Hai Febry,Coba dilampirkan dummy file Excelnya dan apa yang sdh dilakukan.
Salam,HK
On Friday, July 21, 2017, 5:17:38 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:

 
Assalamualaikum, salam sejahtera dan selamat siang para Master dan member milis,

Saya punya kendala dalam pengambilan data dengan menggunakan multiple kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS, SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin bantuan master dan member sekalian. Terima kasih dan sukses selalu.


Sent from Yahoo Mail on Android    #yiv3947373987 #yiv3947373987 -- #yiv3947373987ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3947373987 #yiv3947373987ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3947373987 #yiv3947373987ygrp-mkp #yiv3947373987hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3947373987 #yiv3947373987ygrp-mkp #yiv3947373987ads {margin-bottom:10px;}#yiv3947373987 #yiv3947373987ygrp-mkp .yiv3947373987ad {padding:0 0;}#yiv3947373987 #yiv3947373987ygrp-mkp .yiv3947373987ad p {margin:0;}#yiv3947373987 #yiv3947373987ygrp-mkp .yiv3947373987ad a {color:#0000ff;text-decoration:none;}#yiv3947373987 #yiv3947373987ygrp-sponsor #yiv3947373987ygrp-lc {font-family:Arial;}#yiv3947373987 #yiv3947373987ygrp-sponsor #yiv3947373987ygrp-lc #yiv3947373987hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3947373987 #yiv3947373987ygrp-sponsor #yiv3947373987ygrp-lc .yiv3947373987ad {margin-bottom:10px;padding:0 0;}#yiv3947373987 #yiv3947373987actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3947373987 #yiv3947373987activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3947373987 #yiv3947373987activity span {font-weight:700;}#yiv3947373987 #yiv3947373987activity span:first-child {text-transform:uppercase;}#yiv3947373987 #yiv3947373987activity span a {color:#5085b6;text-decoration:none;}#yiv3947373987 #yiv3947373987activity span span {color:#ff7900;}#yiv3947373987 #yiv3947373987activity span .yiv3947373987underline {text-decoration:underline;}#yiv3947373987 .yiv3947373987attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3947373987 .yiv3947373987attach div a {text-decoration:none;}#yiv3947373987 .yiv3947373987attach img {border:none;padding-right:5px;}#yiv3947373987 .yiv3947373987attach label {display:block;margin-bottom:5px;}#yiv3947373987 .yiv3947373987attach label a {text-decoration:none;}#yiv3947373987 blockquote {margin:0 0 0 4px;}#yiv3947373987 .yiv3947373987bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3947373987 .yiv3947373987bold a {text-decoration:none;}#yiv3947373987 dd.yiv3947373987last p a {font-family:Verdana;font-weight:700;}#yiv3947373987 dd.yiv3947373987last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3947373987 dd.yiv3947373987last p span.yiv3947373987yshortcuts {margin-right:0;}#yiv3947373987 div.yiv3947373987attach-table div div a {text-decoration:none;}#yiv3947373987 div.yiv3947373987attach-table {width:400px;}#yiv3947373987 div.yiv3947373987file-title a, #yiv3947373987 div.yiv3947373987file-title a:active, #yiv3947373987 div.yiv3947373987file-title a:hover, #yiv3947373987 div.yiv3947373987file-title a:visited {text-decoration:none;}#yiv3947373987 div.yiv3947373987photo-title a, #yiv3947373987 div.yiv3947373987photo-title a:active, #yiv3947373987 div.yiv3947373987photo-title a:hover, #yiv3947373987 div.yiv3947373987photo-title a:visited {text-decoration:none;}#yiv3947373987 div#yiv3947373987ygrp-mlmsg #yiv3947373987ygrp-msg p a span.yiv3947373987yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3947373987 .yiv3947373987green {color:#628c2a;}#yiv3947373987 .yiv3947373987MsoNormal {margin:0 0 0 0;}#yiv3947373987 o {font-size:0;}#yiv3947373987 #yiv3947373987photos div {float:left;width:72px;}#yiv3947373987 #yiv3947373987photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3947373987 #yiv3947373987photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3947373987 #yiv3947373987reco-category {font-size:77%;}#yiv3947373987 #yiv3947373987reco-desc {font-size:77%;}#yiv3947373987 .yiv3947373987replbq {margin:4px;}#yiv3947373987 #yiv3947373987ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3947373987 #yiv3947373987ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3947373987 #yiv3947373987ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3947373987 #yiv3947373987ygrp-mlmsg select, #yiv3947373987 input, #yiv3947373987 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3947373987 #yiv3947373987ygrp-mlmsg pre, #yiv3947373987 code {font:115% monospace;}#yiv3947373987 #yiv3947373987ygrp-mlmsg * {line-height:1.22em;}#yiv3947373987 #yiv3947373987ygrp-mlmsg #yiv3947373987logo {padding-bottom:10px;}#yiv3947373987 #yiv3947373987ygrp-msg p a {font-family:Verdana;}#yiv3947373987 #yiv3947373987ygrp-msg p#yiv3947373987attach-count span {color:#1E66AE;font-weight:700;}#yiv3947373987 #yiv3947373987ygrp-reco #yiv3947373987reco-head {color:#ff7900;font-weight:700;}#yiv3947373987 #yiv3947373987ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3947373987 #yiv3947373987ygrp-sponsor #yiv3947373987ov li a {font-size:130%;text-decoration:none;}#yiv3947373987 #yiv3947373987ygrp-sponsor #yiv3947373987ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3947373987 #yiv3947373987ygrp-sponsor #yiv3947373987ov ul {margin:0;padding:0 0 0 8px;}#yiv3947373987 #yiv3947373987ygrp-text {font-family:Georgia;}#yiv3947373987 #yiv3947373987ygrp-text p {margin:0 0 1em 0;}#yiv3947373987 #yiv3947373987ygrp-text tt {font-size:120%;}#yiv3947373987 #yiv3947373987ygrp-vital ul li:last-child {border-right:none !important;}#yiv3947373987

=?UTF-8?b?Y29udG9oIHNvYWwueGxzeA==?= (13K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengambil Data dengan Multiple Kriteria

Milis Belajar Excel mailing list
Hai Fery, (maaf sebelumnya kelebihan "b" krn ngambil dari email address),
Coba copas formula2 ini pada sel B11, J11 dan R11=INDEX($B$5:$G$7,MATCH(B$10,$A$5:$A$7,0),MATCH($A11,$B$4:$G$4,0))
=INDEX($J$5:$O$7,MATCH(J$10,$I$5:$I$7,0),MATCH($I11,$J$4:$O$4,0))
=INDEX($R$5:$W$7,MATCH(R$10,$Q$5:$Q$7,0),MATCH($Q11,$R$4:$W$4,0))

Kemudian copy ke samping dan kebawah.
Salam,HK
On Friday, July 21, 2017, 6:49:05 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:

   

File nya sudah saya lampirkan pak Hendrik. Mohon bimbingannya.

Best Regards,FERY


On Friday, July 21, 2017, 5:33:20 PM GMT+7, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> wrote:

   

Hai Febry,Coba dilampirkan dummy file Excelnya dan apa yang sdh dilakukan.
Salam,HK
On Friday, July 21, 2017, 5:17:38 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:

   
Assalamualaikum, salam sejahtera dan selamat siang para Master dan member milis,

Saya punya kendala dalam pengambilan data dengan menggunakan multiple kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS, SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin bantuan master dan member sekalian. Terima kasih dan sukses selalu.


Sent from Yahoo Mail on Android      
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengambil Data dengan Multiple Kriteria

Milis Belajar Excel mailing list
Hai Pak Hendrik, saya sudah coba copy paste-kan rumus tersebut dan voila, berhasil!
Terima kasih sekali dari saya dan salam hangat dari Bandung.


Best Regards,FERY




On Friday, July 21, 2017, 8:19:53 PM GMT+7, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> wrote:


 


Hai Fery, (maaf sebelumnya kelebihan "b" krn ngambil dari email address),
Coba copas formula2 ini pada sel B11, J11 dan R11=INDEX($B$5:$G$7,MATCH(B$10,$A$5:$A$7,0),MATCH($A11,$B$4:$G$4,0))
=INDEX($J$5:$O$7,MATCH(J$10,$I$5:$I$7,0),MATCH($I11,$J$4:$O$4,0))
=INDEX($R$5:$W$7,MATCH(R$10,$Q$5:$Q$7,0),MATCH($Q11,$R$4:$W$4,0))


Kemudian copy ke samping dan kebawah.
Salam,HK
On Friday, July 21, 2017, 6:49:05 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:


 


File nya sudah saya lampirkan pak Hendrik. Mohon bimbingannya.


Best Regards,FERY




On Friday, July 21, 2017, 5:33:20 PM GMT+7, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> wrote:


 


Hai Febry,Coba dilampirkan dummy file Excelnya dan apa yang sdh dilakukan.
Salam,HK
On Friday, July 21, 2017, 5:17:38 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:


 
Assalamualaikum, salam sejahtera dan selamat siang para Master dan member milis,


Saya punya kendala dalam pengambilan data dengan menggunakan multiple kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS, SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin bantuan master dan member sekalian. Terima kasih dan sukses selalu.




Sent from Yahoo Mail on Android        #yiv3129101024 #yiv3129101024 -- #yiv3129101024ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3129101024 #yiv3129101024ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3129101024 #yiv3129101024ygrp-mkp #yiv3129101024hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3129101024 #yiv3129101024ygrp-mkp #yiv3129101024ads {margin-bottom:10px;}#yiv3129101024 #yiv3129101024ygrp-mkp .yiv3129101024ad {padding:0 0;}#yiv3129101024 #yiv3129101024ygrp-mkp .yiv3129101024ad p {margin:0;}#yiv3129101024 #yiv3129101024ygrp-mkp .yiv3129101024ad a {color:#0000ff;text-decoration:none;}#yiv3129101024 #yiv3129101024ygrp-sponsor #yiv3129101024ygrp-lc {font-family:Arial;}#yiv3129101024 #yiv3129101024ygrp-sponsor #yiv3129101024ygrp-lc #yiv3129101024hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3129101024 #yiv3129101024ygrp-sponsor #yiv3129101024ygrp-lc .yiv3129101024ad {margin-bottom:10px;padding:0 0;}#yiv3129101024 #yiv3129101024actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3129101024 #yiv3129101024activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3129101024 #yiv3129101024activity span {font-weight:700;}#yiv3129101024 #yiv3129101024activity span:first-child {text-transform:uppercase;}#yiv3129101024 #yiv3129101024activity span a {color:#5085b6;text-decoration:none;}#yiv3129101024 #yiv3129101024activity span span {color:#ff7900;}#yiv3129101024 #yiv3129101024activity span .yiv3129101024underline {text-decoration:underline;}#yiv3129101024 .yiv3129101024attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3129101024 .yiv3129101024attach div a {text-decoration:none;}#yiv3129101024 .yiv3129101024attach img {border:none;padding-right:5px;}#yiv3129101024 .yiv3129101024attach label {display:block;margin-bottom:5px;}#yiv3129101024 .yiv3129101024attach label a {text-decoration:none;}#yiv3129101024 blockquote {margin:0 0 0 4px;}#yiv3129101024 .yiv3129101024bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3129101024 .yiv3129101024bold a {text-decoration:none;}#yiv3129101024 dd.yiv3129101024last p a {font-family:Verdana;font-weight:700;}#yiv3129101024 dd.yiv3129101024last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3129101024 dd.yiv3129101024last p span.yiv3129101024yshortcuts {margin-right:0;}#yiv3129101024 div.yiv3129101024attach-table div div a {text-decoration:none;}#yiv3129101024 div.yiv3129101024attach-table {width:400px;}#yiv3129101024 div.yiv3129101024file-title a, #yiv3129101024 div.yiv3129101024file-title a:active, #yiv3129101024 div.yiv3129101024file-title a:hover, #yiv3129101024 div.yiv3129101024file-title a:visited {text-decoration:none;}#yiv3129101024 div.yiv3129101024photo-title a, #yiv3129101024 div.yiv3129101024photo-title a:active, #yiv3129101024 div.yiv3129101024photo-title a:hover, #yiv3129101024 div.yiv3129101024photo-title a:visited {text-decoration:none;}#yiv3129101024 div#yiv3129101024ygrp-mlmsg #yiv3129101024ygrp-msg p a span.yiv3129101024yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3129101024 .yiv3129101024green {color:#628c2a;}#yiv3129101024 .yiv3129101024MsoNormal {margin:0 0 0 0;}#yiv3129101024 o {font-size:0;}#yiv3129101024 #yiv3129101024photos div {float:left;width:72px;}#yiv3129101024 #yiv3129101024photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3129101024 #yiv3129101024photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3129101024 #yiv3129101024reco-category {font-size:77%;}#yiv3129101024 #yiv3129101024reco-desc {font-size:77%;}#yiv3129101024 .yiv3129101024replbq {margin:4px;}#yiv3129101024 #yiv3129101024ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3129101024 #yiv3129101024ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3129101024 #yiv3129101024ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3129101024 #yiv3129101024ygrp-mlmsg select, #yiv3129101024 input, #yiv3129101024 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3129101024 #yiv3129101024ygrp-mlmsg pre, #yiv3129101024 code {font:115% monospace;}#yiv3129101024 #yiv3129101024ygrp-mlmsg * {line-height:1.22em;}#yiv3129101024 #yiv3129101024ygrp-mlmsg #yiv3129101024logo {padding-bottom:10px;}#yiv3129101024 #yiv3129101024ygrp-msg p a {font-family:Verdana;}#yiv3129101024 #yiv3129101024ygrp-msg p#yiv3129101024attach-count span {color:#1E66AE;font-weight:700;}#yiv3129101024 #yiv3129101024ygrp-reco #yiv3129101024reco-head {color:#ff7900;font-weight:700;}#yiv3129101024 #yiv3129101024ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3129101024 #yiv3129101024ygrp-sponsor #yiv3129101024ov li a {font-size:130%;text-decoration:none;}#yiv3129101024 #yiv3129101024ygrp-sponsor #yiv3129101024ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3129101024 #yiv3129101024ygrp-sponsor #yiv3129101024ov ul {margin:0;padding:0 0 0 8px;}#yiv3129101024 #yiv3129101024ygrp-text {font-family:Georgia;}#yiv3129101024 #yiv3129101024ygrp-text p {margin:0 0 1em 0;}#yiv3129101024 #yiv3129101024ygrp-text tt {font-size:120%;}#yiv3129101024 #yiv3129101024ygrp-vital ul li:last-child {border-right:none !important;}#yiv3129101024
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengambil Data dengan Multiple Kriteria

Milis Belajar Excel mailing list
Sama2 Fery.Coba lagi dgn Sumproduct dan tlg share kalau sdh berhasil.
Salam,HK

Sent from Yahoo Mail on Android
 
  On Sat, Jul 22, 2017 at 0:15, Febry Ferian [hidden email] [belajar-excel]<[hidden email]> wrote:      

Hai Pak Hendrik, saya sudah coba copy paste-kan rumus tersebut dan voila, berhasil!
Terima kasih sekali dari saya dan salam hangat dari Bandung.

Best Regards,FERY


On Friday, July 21, 2017, 8:19:53 PM GMT+7, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> wrote:

   

Hai Fery, (maaf sebelumnya kelebihan "b" krn ngambil dari email address),
Coba copas formula2 ini pada sel B11, J11 dan R11=INDEX($B$5:$G$7,MATCH(B$10,$A$5:$A$7,0),MATCH($A11,$B$4:$G$4,0))
=INDEX($J$5:$O$7,MATCH(J$10,$I$5:$I$7,0),MATCH($I11,$J$4:$O$4,0))
=INDEX($R$5:$W$7,MATCH(R$10,$Q$5:$Q$7,0),MATCH($Q11,$R$4:$W$4,0))

Kemudian copy ke samping dan kebawah.
Salam,HK
On Friday, July 21, 2017, 6:49:05 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:

   

File nya sudah saya lampirkan pak Hendrik. Mohon bimbingannya.

Best Regards,FERY


On Friday, July 21, 2017, 5:33:20 PM GMT+7, hendrik karnadi [hidden email] [belajar-excel] <[hidden email]> wrote:

   

Hai Febry,Coba dilampirkan dummy file Excelnya dan apa yang sdh dilakukan.
Salam,HK
On Friday, July 21, 2017, 5:17:38 PM GMT+7, Febry Ferian [hidden email] [belajar-excel] <[hidden email]> wrote:

   
Assalamualaikum, salam sejahtera dan selamat siang para Master dan member milis,

Saya punya kendala dalam pengambilan data dengan menggunakan multiple kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS, SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin bantuan master dan member sekalian. Terima kasih dan sukses selalu.


Sent from Yahoo Mail on Android          #yiv1218288415 #yiv1218288415 -- #yiv1218288415ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1218288415 #yiv1218288415ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1218288415 #yiv1218288415ygrp-mkp #yiv1218288415hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1218288415 #yiv1218288415ygrp-mkp #yiv1218288415ads {margin-bottom:10px;}#yiv1218288415 #yiv1218288415ygrp-mkp .yiv1218288415ad {padding:0 0;}#yiv1218288415 #yiv1218288415ygrp-mkp .yiv1218288415ad p {margin:0;}#yiv1218288415 #yiv1218288415ygrp-mkp .yiv1218288415ad a {color:#0000ff;text-decoration:none;}#yiv1218288415 #yiv1218288415ygrp-sponsor #yiv1218288415ygrp-lc {font-family:Arial;}#yiv1218288415 #yiv1218288415ygrp-sponsor #yiv1218288415ygrp-lc #yiv1218288415hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1218288415 #yiv1218288415ygrp-sponsor #yiv1218288415ygrp-lc .yiv1218288415ad {margin-bottom:10px;padding:0 0;}#yiv1218288415 #yiv1218288415actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1218288415 #yiv1218288415activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1218288415 #yiv1218288415activity span {font-weight:700;}#yiv1218288415 #yiv1218288415activity span:first-child {text-transform:uppercase;}#yiv1218288415 #yiv1218288415activity span a {color:#5085b6;text-decoration:none;}#yiv1218288415 #yiv1218288415activity span span {color:#ff7900;}#yiv1218288415 #yiv1218288415activity span .yiv1218288415underline {text-decoration:underline;}#yiv1218288415 .yiv1218288415attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1218288415 .yiv1218288415attach div a {text-decoration:none;}#yiv1218288415 .yiv1218288415attach img {border:none;padding-right:5px;}#yiv1218288415 .yiv1218288415attach label {display:block;margin-bottom:5px;}#yiv1218288415 .yiv1218288415attach label a {text-decoration:none;}#yiv1218288415 blockquote {margin:0 0 0 4px;}#yiv1218288415 .yiv1218288415bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1218288415 .yiv1218288415bold a {text-decoration:none;}#yiv1218288415 dd.yiv1218288415last p a {font-family:Verdana;font-weight:700;}#yiv1218288415 dd.yiv1218288415last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1218288415 dd.yiv1218288415last p span.yiv1218288415yshortcuts {margin-right:0;}#yiv1218288415 div.yiv1218288415attach-table div div a {text-decoration:none;}#yiv1218288415 div.yiv1218288415attach-table {width:400px;}#yiv1218288415 div.yiv1218288415file-title a, #yiv1218288415 div.yiv1218288415file-title a:active, #yiv1218288415 div.yiv1218288415file-title a:hover, #yiv1218288415 div.yiv1218288415file-title a:visited {text-decoration:none;}#yiv1218288415 div.yiv1218288415photo-title a, #yiv1218288415 div.yiv1218288415photo-title a:active, #yiv1218288415 div.yiv1218288415photo-title a:hover, #yiv1218288415 div.yiv1218288415photo-title a:visited {text-decoration:none;}#yiv1218288415 div#yiv1218288415ygrp-mlmsg #yiv1218288415ygrp-msg p a span.yiv1218288415yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1218288415 .yiv1218288415green {color:#628c2a;}#yiv1218288415 .yiv1218288415MsoNormal {margin:0 0 0 0;}#yiv1218288415 o {font-size:0;}#yiv1218288415 #yiv1218288415photos div {float:left;width:72px;}#yiv1218288415 #yiv1218288415photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1218288415 #yiv1218288415photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1218288415 #yiv1218288415reco-category {font-size:77%;}#yiv1218288415 #yiv1218288415reco-desc {font-size:77%;}#yiv1218288415 .yiv1218288415replbq {margin:4px;}#yiv1218288415 #yiv1218288415ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1218288415 #yiv1218288415ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1218288415 #yiv1218288415ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1218288415 #yiv1218288415ygrp-mlmsg select, #yiv1218288415 input, #yiv1218288415 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1218288415 #yiv1218288415ygrp-mlmsg pre, #yiv1218288415 code {font:115% monospace;}#yiv1218288415 #yiv1218288415ygrp-mlmsg * {line-height:1.22em;}#yiv1218288415 #yiv1218288415ygrp-mlmsg #yiv1218288415logo {padding-bottom:10px;}#yiv1218288415 #yiv1218288415ygrp-msg p a {font-family:Verdana;}#yiv1218288415 #yiv1218288415ygrp-msg p#yiv1218288415attach-count span {color:#1E66AE;font-weight:700;}#yiv1218288415 #yiv1218288415ygrp-reco #yiv1218288415reco-head {color:#ff7900;font-weight:700;}#yiv1218288415 #yiv1218288415ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1218288415 #yiv1218288415ygrp-sponsor #yiv1218288415ov li a {font-size:130%;text-decoration:none;}#yiv1218288415 #yiv1218288415ygrp-sponsor #yiv1218288415ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1218288415 #yiv1218288415ygrp-sponsor #yiv1218288415ov ul {margin:0;padding:0 0 0 8px;}#yiv1218288415 #yiv1218288415ygrp-text {font-family:Georgia;}#yiv1218288415 #yiv1218288415ygrp-text p {margin:0 0 1em 0;}#yiv1218288415 #yiv1218288415ygrp-text tt {font-size:120%;}#yiv1218288415 #yiv1218288415ygrp-vital ul li:last-child {border-right:none !important;}#yiv1218288415  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengambil Data dengan Multiple Kriteria

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list
pinjem formulanya pak HK yang bunyinya :
*=INDEX($B$5:$G$7,MATCH(B$10,$A$5:$A$7,0),MATCH($A11,$B$4:$G$4,0))*

menjadi pakai vLookUp :
=vLookUp( *B$10 , $A$5:$G$7 ,** MATCH($A11 , $A$4:$G$4 , 0 ) ,0 )*

Regards,
Kid


2017-07-21 20:19 GMT+07:00 hendrik karnadi [hidden email]
[belajar-excel] <[hidden email]>:

>
>
> Hai Fery, (maaf sebelumnya kelebihan "b" krn ngambil dari email address),
>
> Coba copas formula2 ini pada sel *B11*, *J11* dan *R11*
> *=INDEX($B$5:$G$7,MATCH(B$10,$A$5:$A$7,0),MATCH($A11,$B$4:$G$4,0))*
>
> *=INDEX($J$5:$O$7,MATCH(J$10,$I$5:$I$7,0),MATCH($I11,$J$4:$O$4,0))*
> *=INDEX($R$5:$W$7,MATCH(R$10,$Q$5:$Q$7,0),MATCH($Q11,$R$4:$W$4,0))*
>
> Kemudian copy ke samping dan kebawah.
>
> Salam,
> HK
>
> ------------------------------
> On Friday, July 21, 2017, 6:49:05 PM GMT+7, Febry Ferian
> [hidden email] [belajar-excel] <[hidden email]>
> wrote:
>
>
>
>
> File nya sudah saya lampirkan pak Hendrik. Mohon bimbingannya.
>
>
> *Best Regards,*
> *FERY*
>
>
>
> On Friday, July 21, 2017, 5:33:20 PM GMT+7, hendrik karnadi
> [hidden email] [belajar-excel] <[hidden email]>
> wrote:
>
>
>
>
> Hai Febry,
> Coba dilampirkan dummy file Excelnya dan apa yang sdh dilakukan.
>
> Salam,
> HK
>
> ------------------------------
> On Friday, July 21, 2017, 5:17:38 PM GMT+7, Febry Ferian
> [hidden email] [belajar-excel] <[hidden email]>
> wrote:
>
>
>
>
> Assalamualaikum, salam sejahtera dan selamat siang para Master dan member
> milis,
>
> Saya punya kendala dalam pengambilan data dengan menggunakan multiple
> kriteria di excel. Saya sudah mencoba browsing tentang penggunaan SUMIFS,
> SUMPRODUCT, maupun INDEX dan LOOKUP serta sejenisnya. Namun saya masih
> belum menemukan solusi seperti yg saya butuhkan. Kesulitan yg ingin saya
> selesaikan ada di dalam file yg saya lampirkan berikut penjelasannya. Mohin
> bantuan master dan member sekalian. Terima kasih dan sukses selalu.
>
>
>
> Sent from Yahoo Mail on Android
> <https://overview.mail.yahoo.com/mobile/?.src=Android>
>
>
>
Loading...