Terimakasih BanyakPak Kid
Pencerahannya sangat sederhana
Regards,Hairul Alam
Pada Jumat, 19 Mei 2017 22:12, "'Mr. Kid' mr.nm...@gmail.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Coba pakai vlookup tanpa kolom bantu. Syaratnya, kolom key sorted asc.Awal
: vlookup( key, area_data , 2 , 0 )Akhir : vlookup( key & "z" , area_data , 2 )
Regards,Kid
Sent from my smart enough phone
On May 19, 2017, at 10:53, nangagus nanga...@gmail.com [belajar-excel]
<belajar-excel@yahoogroups.com> wrote:
Walaikumsalam warahmatullah. Dear Hairul Alam, Pakai kolom bantu yaa.....
di D3 sebagai kolom bantu digunakan formula:
=B3&TEXT(COUNTIF($B$3:B3;B3);"00") copy formula ini sampai D21
di F3 digunakan formula:
=INDEX($C$3:$C$21;MATCH(E3&"01";$D$3:$D$21;0)) sedangkan di G3 formulanya:
=INDEX($C$3:$C$21;MATCH(E3&TEXT(COUNTIF($B$3:$B$21;E3);"00");$D$3:$D$21;0))
Jangan lupa ganti tanda ; menjadi , jika regional settingnya berbahasa inggris
salam NangAgus
On 19/05/17 08.17, HAIRUL ALAM ha_m...@yahoo.co.id [belajar-excel] wrote:
Assalamualaikum Wr. Wb. Para Master Excel
Maaf mengganggu untuk semuanya
Saya hendak menentukan data awal dan data akhir berdasarkan kode tertentu
seperti data terlampir bagaimanakah formulanya agar data bisa ditentukan sesuai
lampiran cell "F3" & "F4" yang saya beri warna kuning.
Terimakasih Banyak Wassalamualaikum Wr. Wb.
Hairul Alam
#yiv4100036361 #yiv4100036361 -- #yiv4100036361ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4100036361
#yiv4100036361ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4100036361
#yiv4100036361ygrp-mkp #yiv4100036361hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv4100036361 #yiv4100036361ygrp-mkp #yiv4100036361ads
{margin-bottom:10px;}#yiv4100036361 #yiv4100036361ygrp-mkp .yiv4100036361ad
{padding:0 0;}#yiv4100036361 #yiv4100036361ygrp-mkp .yiv4100036361ad p
{margin:0;}#yiv4100036361 #yiv4100036361ygrp-mkp .yiv4100036361ad a
{color:#0000ff;text-decoration:none;}#yiv4100036361 #yiv4100036361ygrp-sponsor
#yiv4100036361ygrp-lc {font-family:Arial;}#yiv4100036361
#yiv4100036361ygrp-sponsor #yiv4100036361ygrp-lc #yiv4100036361hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4100036361
#yiv4100036361ygrp-sponsor #yiv4100036361ygrp-lc .yiv4100036361ad
{margin-bottom:10px;padding:0 0;}#yiv4100036361 #yiv4100036361actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4100036361
#yiv4100036361activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4100036361
#yiv4100036361activity span {font-weight:700;}#yiv4100036361
#yiv4100036361activity span:first-child
{text-transform:uppercase;}#yiv4100036361 #yiv4100036361activity span a
{color:#5085b6;text-decoration:none;}#yiv4100036361 #yiv4100036361activity span
span {color:#ff7900;}#yiv4100036361 #yiv4100036361activity span
.yiv4100036361underline {text-decoration:underline;}#yiv4100036361
.yiv4100036361attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv4100036361 .yiv4100036361attach div a
{text-decoration:none;}#yiv4100036361 .yiv4100036361attach img
{border:none;padding-right:5px;}#yiv4100036361 .yiv4100036361attach label
{display:block;margin-bottom:5px;}#yiv4100036361 .yiv4100036361attach label a
{text-decoration:none;}#yiv4100036361 blockquote {margin:0 0 0
4px;}#yiv4100036361 .yiv4100036361bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv4100036361
.yiv4100036361bold a {text-decoration:none;}#yiv4100036361 dd.yiv4100036361last
p a {font-family:Verdana;font-weight:700;}#yiv4100036361 dd.yiv4100036361last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4100036361
dd.yiv4100036361last p span.yiv4100036361yshortcuts
{margin-right:0;}#yiv4100036361 div.yiv4100036361attach-table div div a
{text-decoration:none;}#yiv4100036361 div.yiv4100036361attach-table
{width:400px;}#yiv4100036361 div.yiv4100036361file-title a, #yiv4100036361
div.yiv4100036361file-title a:active, #yiv4100036361
div.yiv4100036361file-title a:hover, #yiv4100036361 div.yiv4100036361file-title
a:visited {text-decoration:none;}#yiv4100036361 div.yiv4100036361photo-title a,
#yiv4100036361 div.yiv4100036361photo-title a:active, #yiv4100036361
div.yiv4100036361photo-title a:hover, #yiv4100036361
div.yiv4100036361photo-title a:visited {text-decoration:none;}#yiv4100036361
div#yiv4100036361ygrp-mlmsg #yiv4100036361ygrp-msg p a
span.yiv4100036361yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4100036361
.yiv4100036361green {color:#628c2a;}#yiv4100036361 .yiv4100036361MsoNormal
{margin:0 0 0 0;}#yiv4100036361 o {font-size:0;}#yiv4100036361
#yiv4100036361photos div {float:left;width:72px;}#yiv4100036361
#yiv4100036361photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv4100036361
#yiv4100036361photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4100036361
#yiv4100036361reco-category {font-size:77%;}#yiv4100036361
#yiv4100036361reco-desc {font-size:77%;}#yiv4100036361 .yiv4100036361replbq
{margin:4px;}#yiv4100036361 #yiv4100036361ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv4100036361 #yiv4100036361ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4100036361
#yiv4100036361ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4100036361
#yiv4100036361ygrp-mlmsg select, #yiv4100036361 input, #yiv4100036361 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv4100036361
#yiv4100036361ygrp-mlmsg pre, #yiv4100036361 code {font:115%
monospace;}#yiv4100036361 #yiv4100036361ygrp-mlmsg *
{line-height:1.22em;}#yiv4100036361 #yiv4100036361ygrp-mlmsg #yiv4100036361logo
{padding-bottom:10px;}#yiv4100036361 #yiv4100036361ygrp-msg p a
{font-family:Verdana;}#yiv4100036361 #yiv4100036361ygrp-msg
p#yiv4100036361attach-count span {color:#1E66AE;font-weight:700;}#yiv4100036361
#yiv4100036361ygrp-reco #yiv4100036361reco-head
{color:#ff7900;font-weight:700;}#yiv4100036361 #yiv4100036361ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv4100036361 #yiv4100036361ygrp-sponsor
#yiv4100036361ov li a {font-size:130%;text-decoration:none;}#yiv4100036361
#yiv4100036361ygrp-sponsor #yiv4100036361ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv4100036361
#yiv4100036361ygrp-sponsor #yiv4100036361ov ul {margin:0;padding:0 0 0
8px;}#yiv4100036361 #yiv4100036361ygrp-text
{font-family:Georgia;}#yiv4100036361 #yiv4100036361ygrp-text p {margin:0 0 1em
0;}#yiv4100036361 #yiv4100036361ygrp-text tt {font-size:120%;}#yiv4100036361
#yiv4100036361ygrp-vital ul li:last-child {border-right:none
!important;}#yiv4100036361
