Terimakasih mas Mr.Kid
On Thursday, 8 October 2015, 15:01, "'Mr. Kid' [email protected]
[belajar-excel]" <[email protected]> wrote:
Mungkin begini : (array formula alias diakhir dengan menekan CTRl SHIFT
ENTER)
=INDEX($F$2:$F$9;MATCH($L3&M$2;$A$2:$A$9&$B$2:$B$9;0))
atau menjadi formula biasa (bukan array formula)
=INDEX($F$2:$F$9;MATCH($L3&M$2;Index( $A$2:$A$9&$B$2:$B$9 ; 0 ) ;0))
atau
=INDEX($F$2:$F$9;MATCH( 1 ;Index( ($A$2:$A$9=$L3)*($B$2:$B$9=M$2) ; 0 ) ;0))
kalau Index (yang di-bold) dilepas, maka formula di atas harus menjadi array
formula kembali.
Biasanya bunyi formula yang terakhir itu bisa memanfaatkan LookUp seperti ini :
=LookUp( 2 ; 1 / ( ($A$2:$A$9=$L3)*($B$2:$B$9=M$2) ) ; $F$2:$F$9 ) walau tanpa
Index (yang di-bold di atas sana), formula dengan fungsi LookUp ini tidak perlu
di enter sebagai array formula.
Formula dengan lookup yang berisi filter data berdasar suatu kriteria adalah
contoh pemanfaatan tipe data Error Value dalam pengolahan data.
Formula LookUp di atas bisa jadi akan lebih mudah dipahami jika diubah menjadi
array formula berikut : (karena pakai fungsi IF)
=LookUp( 2 ; IF ( ($A$2:$A$9=$L3)*($B$2:$B$9=M$2) ; 1 ; "" ) ; $F$2:$F$9 )
atau
=LookUp( 2 ; IF ( ($A$2:$A$9=$L3)*($B$2:$B$9=M$2) ; 1 ) ; $F$2:$F$9 )
Wassalam,
Kid
2015-10-08 14:47 GMT+07:00 [email protected] [belajar-excel]
<[email protected]>:
Dear All,
Sudah bisa, masalah selesai. dapatnya seperti ini.
=INDEX($F$2:$F$9;MATCH($L$3&M2;$A$2:$A$9&$B$2:$B$9;0))
cuma lemahnya formula diatas, jika di copy ke bawah, tidak bisa.
Terimakasih
#yiv8853370260 #yiv8853370260 -- #yiv8853370260ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8853370260
#yiv8853370260ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8853370260
#yiv8853370260ygrp-mkp #yiv8853370260hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8853370260 #yiv8853370260ygrp-mkp #yiv8853370260ads
{margin-bottom:10px;}#yiv8853370260 #yiv8853370260ygrp-mkp .yiv8853370260ad
{padding:0 0;}#yiv8853370260 #yiv8853370260ygrp-mkp .yiv8853370260ad p
{margin:0;}#yiv8853370260 #yiv8853370260ygrp-mkp .yiv8853370260ad a
{color:#0000ff;text-decoration:none;}#yiv8853370260 #yiv8853370260ygrp-sponsor
#yiv8853370260ygrp-lc {font-family:Arial;}#yiv8853370260
#yiv8853370260ygrp-sponsor #yiv8853370260ygrp-lc #yiv8853370260hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8853370260
#yiv8853370260ygrp-sponsor #yiv8853370260ygrp-lc .yiv8853370260ad
{margin-bottom:10px;padding:0 0;}#yiv8853370260 #yiv8853370260actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8853370260
#yiv8853370260activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8853370260
#yiv8853370260activity span {font-weight:700;}#yiv8853370260
#yiv8853370260activity span:first-child
{text-transform:uppercase;}#yiv8853370260 #yiv8853370260activity span a
{color:#5085b6;text-decoration:none;}#yiv8853370260 #yiv8853370260activity span
span {color:#ff7900;}#yiv8853370260 #yiv8853370260activity span
.yiv8853370260underline {text-decoration:underline;}#yiv8853370260
.yiv8853370260attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8853370260 .yiv8853370260attach div a
{text-decoration:none;}#yiv8853370260 .yiv8853370260attach img
{border:none;padding-right:5px;}#yiv8853370260 .yiv8853370260attach label
{display:block;margin-bottom:5px;}#yiv8853370260 .yiv8853370260attach label a
{text-decoration:none;}#yiv8853370260 blockquote {margin:0 0 0
4px;}#yiv8853370260 .yiv8853370260bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8853370260
.yiv8853370260bold a {text-decoration:none;}#yiv8853370260 dd.yiv8853370260last
p a {font-family:Verdana;font-weight:700;}#yiv8853370260 dd.yiv8853370260last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8853370260
dd.yiv8853370260last p span.yiv8853370260yshortcuts
{margin-right:0;}#yiv8853370260 div.yiv8853370260attach-table div div a
{text-decoration:none;}#yiv8853370260 div.yiv8853370260attach-table
{width:400px;}#yiv8853370260 div.yiv8853370260file-title a, #yiv8853370260
div.yiv8853370260file-title a:active, #yiv8853370260
div.yiv8853370260file-title a:hover, #yiv8853370260 div.yiv8853370260file-title
a:visited {text-decoration:none;}#yiv8853370260 div.yiv8853370260photo-title a,
#yiv8853370260 div.yiv8853370260photo-title a:active, #yiv8853370260
div.yiv8853370260photo-title a:hover, #yiv8853370260
div.yiv8853370260photo-title a:visited {text-decoration:none;}#yiv8853370260
div#yiv8853370260ygrp-mlmsg #yiv8853370260ygrp-msg p a
span.yiv8853370260yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8853370260
.yiv8853370260green {color:#628c2a;}#yiv8853370260 .yiv8853370260MsoNormal
{margin:0 0 0 0;}#yiv8853370260 o {font-size:0;}#yiv8853370260
#yiv8853370260photos div {float:left;width:72px;}#yiv8853370260
#yiv8853370260photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv8853370260
#yiv8853370260photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8853370260
#yiv8853370260reco-category {font-size:77%;}#yiv8853370260
#yiv8853370260reco-desc {font-size:77%;}#yiv8853370260 .yiv8853370260replbq
{margin:4px;}#yiv8853370260 #yiv8853370260ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8853370260 #yiv8853370260ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8853370260
#yiv8853370260ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8853370260
#yiv8853370260ygrp-mlmsg select, #yiv8853370260 input, #yiv8853370260 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8853370260
#yiv8853370260ygrp-mlmsg pre, #yiv8853370260 code {font:115%
monospace;}#yiv8853370260 #yiv8853370260ygrp-mlmsg *
{line-height:1.22em;}#yiv8853370260 #yiv8853370260ygrp-mlmsg #yiv8853370260logo
{padding-bottom:10px;}#yiv8853370260 #yiv8853370260ygrp-msg p a
{font-family:Verdana;}#yiv8853370260 #yiv8853370260ygrp-msg
p#yiv8853370260attach-count span {color:#1E66AE;font-weight:700;}#yiv8853370260
#yiv8853370260ygrp-reco #yiv8853370260reco-head
{color:#ff7900;font-weight:700;}#yiv8853370260 #yiv8853370260ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8853370260 #yiv8853370260ygrp-sponsor
#yiv8853370260ov li a {font-size:130%;text-decoration:none;}#yiv8853370260
#yiv8853370260ygrp-sponsor #yiv8853370260ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8853370260
#yiv8853370260ygrp-sponsor #yiv8853370260ov ul {margin:0;padding:0 0 0
8px;}#yiv8853370260 #yiv8853370260ygrp-text
{font-family:Georgia;}#yiv8853370260 #yiv8853370260ygrp-text p {margin:0 0 1em
0;}#yiv8853370260 #yiv8853370260ygrp-text tt {font-size:120%;}#yiv8853370260
#yiv8853370260ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8853370260
