[belajar-excel] mohon penjelasan tentang lookup data [1 Attachment]

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

[belajar-excel] mohon penjelasan tentang lookup data [1 Attachment]

Milis Belajar Excel mailing list


 السَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
 ​bagaimana format untuk data berikut (lampiran)​


  وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
 

 


















 


Book1.xlsx (14K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] mohon penjelasan tentang lookup data [1 Attachment]

Milis Belajar Excel mailing list
Waalaikumsalam Wr. Wb.Mas Syaiful Hanan
Mungkin dengan rumus :Dengan VLOOKUPB4 =IFERROR(VLOOKUP(A4,persediaan!$A$3:$F$6,2,FALSE),"")C4 =IFERROR(VLOOKUP(A4,persediaan!$A$3:$F$6,3,FALSE),"")D4 =C4*IFERROR(VLOOKUP(A4,persediaan!$A$3:$F$6,r,FALSE),"")Dengan INDEX dan MATCHB5 =INDEX(persediaan!$A$3:$F$6,MATCH(A5,persediaan!$A$3:$A$6,0),2)C5 =INDEX(persediaan!$A$3:$F$6,MATCH(A5,persediaan!$A$3:$A$6,0),3)D5 =C5*INDEX(persediaan!$A$3:$F$6,MATCH(A5,persediaan!$A$3:$A$6,0),6)
sesuai dengan lampiran semoga bisa membantu, Sama2 belajar yaMaaf bila tidak sesuai dengan yang diharapkan
Wassalamualaikum Wr. Wb.Hairul Alam


    Pada Sabtu, 20 Mei 2017 12:55, "Syaiful Hanan [hidden email] [belajar-excel]" <[hidden email]> menulis:



  
السَّلَامُعَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ​bagaimana format untuk data berikut (lampiran)​
 وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ 


  #yiv1390943259 #yiv1390943259 -- #yiv1390943259ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1390943259 #yiv1390943259ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1390943259 #yiv1390943259ygrp-mkp #yiv1390943259hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1390943259 #yiv1390943259ygrp-mkp #yiv1390943259ads {margin-bottom:10px;}#yiv1390943259 #yiv1390943259ygrp-mkp .yiv1390943259ad {padding:0 0;}#yiv1390943259 #yiv1390943259ygrp-mkp .yiv1390943259ad p {margin:0;}#yiv1390943259 #yiv1390943259ygrp-mkp .yiv1390943259ad a {color:#0000ff;text-decoration:none;}#yiv1390943259 #yiv1390943259ygrp-sponsor #yiv1390943259ygrp-lc {font-family:Arial;}#yiv1390943259 #yiv1390943259ygrp-sponsor #yiv1390943259ygrp-lc #yiv1390943259hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1390943259 #yiv1390943259ygrp-sponsor #yiv1390943259ygrp-lc .yiv1390943259ad {margin-bottom:10px;padding:0 0;}#yiv1390943259 #yiv1390943259actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1390943259 #yiv1390943259activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1390943259 #yiv1390943259activity span {font-weight:700;}#yiv1390943259 #yiv1390943259activity span:first-child {text-transform:uppercase;}#yiv1390943259 #yiv1390943259activity span a {color:#5085b6;text-decoration:none;}#yiv1390943259 #yiv1390943259activity span span {color:#ff7900;}#yiv1390943259 #yiv1390943259activity span .yiv1390943259underline {text-decoration:underline;}#yiv1390943259 .yiv1390943259attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1390943259 .yiv1390943259attach div a {text-decoration:none;}#yiv1390943259 .yiv1390943259attach img {border:none;padding-right:5px;}#yiv1390943259 .yiv1390943259attach label {display:block;margin-bottom:5px;}#yiv1390943259 .yiv1390943259attach label a {text-decoration:none;}#yiv1390943259 blockquote {margin:0 0 0 4px;}#yiv1390943259 .yiv1390943259bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1390943259 .yiv1390943259bold a {text-decoration:none;}#yiv1390943259 dd.yiv1390943259last p a {font-family:Verdana;font-weight:700;}#yiv1390943259 dd.yiv1390943259last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1390943259 dd.yiv1390943259last p span.yiv1390943259yshortcuts {margin-right:0;}#yiv1390943259 div.yiv1390943259attach-table div div a {text-decoration:none;}#yiv1390943259 div.yiv1390943259attach-table {width:400px;}#yiv1390943259 div.yiv1390943259file-title a, #yiv1390943259 div.yiv1390943259file-title a:active, #yiv1390943259 div.yiv1390943259file-title a:hover, #yiv1390943259 div.yiv1390943259file-title a:visited {text-decoration:none;}#yiv1390943259 div.yiv1390943259photo-title a, #yiv1390943259 div.yiv1390943259photo-title a:active, #yiv1390943259 div.yiv1390943259photo-title a:hover, #yiv1390943259 div.yiv1390943259photo-title a:visited {text-decoration:none;}#yiv1390943259 div#yiv1390943259ygrp-mlmsg #yiv1390943259ygrp-msg p a span.yiv1390943259yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1390943259 .yiv1390943259green {color:#628c2a;}#yiv1390943259 .yiv1390943259MsoNormal {margin:0 0 0 0;}#yiv1390943259 o {font-size:0;}#yiv1390943259 #yiv1390943259photos div {float:left;width:72px;}#yiv1390943259 #yiv1390943259photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1390943259 #yiv1390943259photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1390943259 #yiv1390943259reco-category {font-size:77%;}#yiv1390943259 #yiv1390943259reco-desc {font-size:77%;}#yiv1390943259 .yiv1390943259replbq {margin:4px;}#yiv1390943259 #yiv1390943259ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1390943259 #yiv1390943259ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1390943259 #yiv1390943259ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1390943259 #yiv1390943259ygrp-mlmsg select, #yiv1390943259 input, #yiv1390943259 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1390943259 #yiv1390943259ygrp-mlmsg pre, #yiv1390943259 code {font:115% monospace;}#yiv1390943259 #yiv1390943259ygrp-mlmsg * {line-height:1.22em;}#yiv1390943259 #yiv1390943259ygrp-mlmsg #yiv1390943259logo {padding-bottom:10px;}#yiv1390943259 #yiv1390943259ygrp-msg p a {font-family:Verdana;}#yiv1390943259 #yiv1390943259ygrp-msg p#yiv1390943259attach-count span {color:#1E66AE;font-weight:700;}#yiv1390943259 #yiv1390943259ygrp-reco #yiv1390943259reco-head {color:#ff7900;font-weight:700;}#yiv1390943259 #yiv1390943259ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1390943259 #yiv1390943259ygrp-sponsor #yiv1390943259ov li a {font-size:130%;text-decoration:none;}#yiv1390943259 #yiv1390943259ygrp-sponsor #yiv1390943259ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1390943259 #yiv1390943259ygrp-sponsor #yiv1390943259ov ul {margin:0;padding:0 0 0 8px;}#yiv1390943259 #yiv1390943259ygrp-text {font-family:Georgia;}#yiv1390943259 #yiv1390943259ygrp-text p {margin:0 0 1em 0;}#yiv1390943259 #yiv1390943259ygrp-text tt {font-size:120%;}#yiv1390943259 #yiv1390943259ygrp-vital ul li:last-child {border-right:none !important;}#yiv1390943259



Book1 Re-Lookup.xlsx (15K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] mohon penjelasan tentang lookup data

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list
Hai Syaiful,Agar data lebih "fleksibel" insert Tabel dengan nama, mis. "Data" pada range("A2:F6") sheet Persediaan (My table has headers dicentang).
Pada Sheet Penjualan sel B4 ketik formula =IF($A4="","",VLOOKUP($A4,Data,2))Pada Sheet Penjualan sel D4 ketik formula =IF($A4="","",VLOOKUP($A4,Data,6)*C4)
Kemudian copy ke bawah.
Selamat mencoba.
Salam,HK
      From: "Syaiful Hanan [hidden email] [belajar-excel]" <[hidden email]>
 To: [hidden email]
 Sent: Saturday, 20 May 2017, 11:55
 Subject: [belajar-excel] mohon penjelasan tentang lookup data [1 Attachment]
   
   
السَّلَامُعَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ​bagaimana format untuk data berikut (lampiran)​
 وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ 

  #yiv9999151722 #yiv9999151722 -- #yiv9999151722ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9999151722 #yiv9999151722ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9999151722 #yiv9999151722ygrp-mkp #yiv9999151722hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9999151722 #yiv9999151722ygrp-mkp #yiv9999151722ads {margin-bottom:10px;}#yiv9999151722 #yiv9999151722ygrp-mkp .yiv9999151722ad {padding:0 0;}#yiv9999151722 #yiv9999151722ygrp-mkp .yiv9999151722ad p {margin:0;}#yiv9999151722 #yiv9999151722ygrp-mkp .yiv9999151722ad a {color:#0000ff;text-decoration:none;}#yiv9999151722 #yiv9999151722ygrp-sponsor #yiv9999151722ygrp-lc {font-family:Arial;}#yiv9999151722 #yiv9999151722ygrp-sponsor #yiv9999151722ygrp-lc #yiv9999151722hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9999151722 #yiv9999151722ygrp-sponsor #yiv9999151722ygrp-lc .yiv9999151722ad {margin-bottom:10px;padding:0 0;}#yiv9999151722 #yiv9999151722actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9999151722 #yiv9999151722activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9999151722 #yiv9999151722activity span {font-weight:700;}#yiv9999151722 #yiv9999151722activity span:first-child {text-transform:uppercase;}#yiv9999151722 #yiv9999151722activity span a {color:#5085b6;text-decoration:none;}#yiv9999151722 #yiv9999151722activity span span {color:#ff7900;}#yiv9999151722 #yiv9999151722activity span .yiv9999151722underline {text-decoration:underline;}#yiv9999151722 .yiv9999151722attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9999151722 .yiv9999151722attach div a {text-decoration:none;}#yiv9999151722 .yiv9999151722attach img {border:none;padding-right:5px;}#yiv9999151722 .yiv9999151722attach label {display:block;margin-bottom:5px;}#yiv9999151722 .yiv9999151722attach label a {text-decoration:none;}#yiv9999151722 blockquote {margin:0 0 0 4px;}#yiv9999151722 .yiv9999151722bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9999151722 .yiv9999151722bold a {text-decoration:none;}#yiv9999151722 dd.yiv9999151722last p a {font-family:Verdana;font-weight:700;}#yiv9999151722 dd.yiv9999151722last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9999151722 dd.yiv9999151722last p span.yiv9999151722yshortcuts {margin-right:0;}#yiv9999151722 div.yiv9999151722attach-table div div a {text-decoration:none;}#yiv9999151722 div.yiv9999151722attach-table {width:400px;}#yiv9999151722 div.yiv9999151722file-title a, #yiv9999151722 div.yiv9999151722file-title a:active, #yiv9999151722 div.yiv9999151722file-title a:hover, #yiv9999151722 div.yiv9999151722file-title a:visited {text-decoration:none;}#yiv9999151722 div.yiv9999151722photo-title a, #yiv9999151722 div.yiv9999151722photo-title a:active, #yiv9999151722 div.yiv9999151722photo-title a:hover, #yiv9999151722 div.yiv9999151722photo-title a:visited {text-decoration:none;}#yiv9999151722 div#yiv9999151722ygrp-mlmsg #yiv9999151722ygrp-msg p a span.yiv9999151722yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9999151722 .yiv9999151722green {color:#628c2a;}#yiv9999151722 .yiv9999151722MsoNormal {margin:0 0 0 0;}#yiv9999151722 o {font-size:0;}#yiv9999151722 #yiv9999151722photos div {float:left;width:72px;}#yiv9999151722 #yiv9999151722photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9999151722 #yiv9999151722photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9999151722 #yiv9999151722reco-category {font-size:77%;}#yiv9999151722 #yiv9999151722reco-desc {font-size:77%;}#yiv9999151722 .yiv9999151722replbq {margin:4px;}#yiv9999151722 #yiv9999151722ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9999151722 #yiv9999151722ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9999151722 #yiv9999151722ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9999151722 #yiv9999151722ygrp-mlmsg select, #yiv9999151722 input, #yiv9999151722 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9999151722 #yiv9999151722ygrp-mlmsg pre, #yiv9999151722 code {font:115% monospace;}#yiv9999151722 #yiv9999151722ygrp-mlmsg * {line-height:1.22em;}#yiv9999151722 #yiv9999151722ygrp-mlmsg #yiv9999151722logo {padding-bottom:10px;}#yiv9999151722 #yiv9999151722ygrp-msg p a {font-family:Verdana;}#yiv9999151722 #yiv9999151722ygrp-msg p#yiv9999151722attach-count span {color:#1E66AE;font-weight:700;}#yiv9999151722 #yiv9999151722ygrp-reco #yiv9999151722reco-head {color:#ff7900;font-weight:700;}#yiv9999151722 #yiv9999151722ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9999151722 #yiv9999151722ygrp-sponsor #yiv9999151722ov li a {font-size:130%;text-decoration:none;}#yiv9999151722 #yiv9999151722ygrp-sponsor #yiv9999151722ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9999151722 #yiv9999151722ygrp-sponsor #yiv9999151722ov ul {margin:0;padding:0 0 0 8px;}#yiv9999151722 #yiv9999151722ygrp-text {font-family:Georgia;}#yiv9999151722 #yiv9999151722ygrp-text p {margin:0 0 1em 0;}#yiv9999151722 #yiv9999151722ygrp-text tt {font-size:120%;}#yiv9999151722 #yiv9999151722ygrp-vital ul li:last-child {border-right:none !important;}#yiv9999151722

   
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] mohon penjelasan tentang lookup data

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list
Coba di B4 diketik formula:
=INDEX(persediaan!B$3:B$6,MATCH($A4,persediaan!$A$3:$A$6))
copy formula tersebut sampai C7
jika perlu tambahkan formula IFERROR
pada D4 ketik formula:
=INDEX(persediaan!F$3:F$6,MATCH($A4,persediaan!$A$3:$A$6))*C4
kopi sampai D7.


2017-05-19 19:52 GMT-07:00 Syaiful Hanan [hidden email]
[belajar-excel] <[hidden email]>:

>
>
>
> السَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
> ​bagaimana format untuk data berikut (lampiran)​
>
>  وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>
>
>
>
>
Loading...