[milis-belajar-excel] Vlookup banyak kolom

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[milis-belajar-excel] Vlookup banyak kolom

Adi Abdullah
Dear teman2 milis excel.
Mohon bantuan nya, bagaimana caranya untuk mendapatkan nilai kode PO sesuai dengan kriteria. Sementara kolom yg menjadi acuan nya terdiri dri beberapa kolom.
Mohon bantuan nya, saya coba menggunakan rumus vlookup tapi tidak berhasil. 
Mohon maap jika pertanyaan nya sedikit membingungkan. 
File contoh terlampir.
Sebelumnya saya ucapkan terima kasih.

Best Regards,
Adi 

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAK15MAC3OgaUiT81kCLUwTH8fDMWTu7DY3uL8jcbLyQUVUoECw%40mail.gmail.com.

qa.xlsx (14K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Vlookup banyak kolom

JAKA KAMIRIN
Coba pake rumus sbb di kolom N3:
=IFERROR(VLOOKUP(M3;$B$3:$J$19;9;0);"")

smoga bisa mmbantu

Thanks

Pada tanggal Sel, 5 Jan 2021 pukul 15.11 Adi Abdullah <[hidden email]> menulis:
Dear teman2 milis excel.
Mohon bantuan nya, bagaimana caranya untuk mendapatkan nilai kode PO sesuai dengan kriteria. Sementara kolom yg menjadi acuan nya terdiri dri beberapa kolom.
Mohon bantuan nya, saya coba menggunakan rumus vlookup tapi tidak berhasil. 
Mohon maap jika pertanyaan nya sedikit membingungkan. 
File contoh terlampir.
Sebelumnya saya ucapkan terima kasih.

Best Regards,
Adi 

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAK15MAC3OgaUiT81kCLUwTH8fDMWTu7DY3uL8jcbLyQUVUoECw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAP5F6bVfEb%3Dy3cicKrS51NOGMC5pxgG3o26AgoADJeR2aaqJ8w%40mail.gmail.com.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Vlookup banyak kolom

Kid
Administrator
In reply to this post by Adi Abdullah

Pakai vLookUp ya...
1. buat kolom berformula sebelum tabel (misal di kolom A), berisi formula untuk menggabungkan semua key lookup dengan selalu diapit delimiter tertentu
    ="|"&cellKode1&"|"&cellKode2&"|" dst
2. copy formula nomor 1 ka baris seluruh baris data
3. di cells hasil, buat formula vLookUp, dengan nilai yang dicari adalah teks kriteria yang diapit delimiter dan memanfaatkan wildcard chararacter untuk mencari 'yang berisi'
   =vLookUp( "*|"&cellsKriteria&"|*",tabelDataMulaiKolomHasilNomor1,nomor_kolom_yangdiambil,FALSE)
4. copy formula nomor 3 ke seluruh cells hasil
5. save

Regards,
Kid




On Tue, Jan 5, 2021 at 3:11 PM Adi Abdullah <[hidden email]> wrote:
Dear teman2 milis excel.
Mohon bantuan nya, bagaimana caranya untuk mendapatkan nilai kode PO sesuai dengan kriteria. Sementara kolom yg menjadi acuan nya terdiri dri beberapa kolom.
Mohon bantuan nya, saya coba menggunakan rumus vlookup tapi tidak berhasil. 
Mohon maap jika pertanyaan nya sedikit membingungkan. 
File contoh terlampir.
Sebelumnya saya ucapkan terima kasih.

Best Regards,
Adi 

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAK15MAC3OgaUiT81kCLUwTH8fDMWTu7DY3uL8jcbLyQUVUoECw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPuBoVd%2BbkmDie8D_bXb_L-ZKcGYFcgq-VM5%2BxMK_bjMCw%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Vlookup banyak kolom

JAKA KAMIRIN
Dear Pa  Kid,

Rumusnya sudah berjalan dengan sangat baik,
=VLOOKUP("*|"&M3&"|*";$A$3:$J$19;10;FALSE)

ini rumus A3nya
="|"&B3&"|"&D3&"|"&F3&"|"&H3&"|"

Terima Kasih yaa atas bantuannya

Pada tanggal Sel, 5 Jan 2021 pukul 16.30 Mr. Kid <[hidden email]> menulis:

Pakai vLookUp ya...
1. buat kolom berformula sebelum tabel (misal di kolom A), berisi formula untuk menggabungkan semua key lookup dengan selalu diapit delimiter tertentu
    ="|"&cellKode1&"|"&cellKode2&"|" dst
2. copy formula nomor 1 ka baris seluruh baris data
3. di cells hasil, buat formula vLookUp, dengan nilai yang dicari adalah teks kriteria yang diapit delimiter dan memanfaatkan wildcard chararacter untuk mencari 'yang berisi'
   =vLookUp( "*|"&cellsKriteria&"|*",tabelDataMulaiKolomHasilNomor1,nomor_kolom_yangdiambil,FALSE)
4. copy formula nomor 3 ke seluruh cells hasil
5. save

Regards,
Kid




On Tue, Jan 5, 2021 at 3:11 PM Adi Abdullah <[hidden email]> wrote:
Dear teman2 milis excel.
Mohon bantuan nya, bagaimana caranya untuk mendapatkan nilai kode PO sesuai dengan kriteria. Sementara kolom yg menjadi acuan nya terdiri dri beberapa kolom.
Mohon bantuan nya, saya coba menggunakan rumus vlookup tapi tidak berhasil. 
Mohon maap jika pertanyaan nya sedikit membingungkan. 
File contoh terlampir.
Sebelumnya saya ucapkan terima kasih.

Best Regards,
Adi 

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAK15MAC3OgaUiT81kCLUwTH8fDMWTu7DY3uL8jcbLyQUVUoECw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPuBoVd%2BbkmDie8D_bXb_L-ZKcGYFcgq-VM5%2BxMK_bjMCw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAP5F6bUjkx0OGHaVAFKar2BP5kVszWMdsk7rihfEAz--2kUkdA%40mail.gmail.com.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Vlookup banyak kolom

Kid
Administrator
sip sip

On Tue, Jan 5, 2021 at 4:59 PM JAKA KAMIRIN <[hidden email]> wrote:
Dear Pa  Kid,

Rumusnya sudah berjalan dengan sangat baik,
=VLOOKUP("*|"&M3&"|*";$A$3:$J$19;10;FALSE)

ini rumus A3nya
="|"&B3&"|"&D3&"|"&F3&"|"&H3&"|"

Terima Kasih yaa atas bantuannya

Pada tanggal Sel, 5 Jan 2021 pukul 16.30 Mr. Kid <[hidden email]> menulis:

Pakai vLookUp ya...
1. buat kolom berformula sebelum tabel (misal di kolom A), berisi formula untuk menggabungkan semua key lookup dengan selalu diapit delimiter tertentu
    ="|"&cellKode1&"|"&cellKode2&"|" dst
2. copy formula nomor 1 ka baris seluruh baris data
3. di cells hasil, buat formula vLookUp, dengan nilai yang dicari adalah teks kriteria yang diapit delimiter dan memanfaatkan wildcard chararacter untuk mencari 'yang berisi'
   =vLookUp( "*|"&cellsKriteria&"|*",tabelDataMulaiKolomHasilNomor1,nomor_kolom_yangdiambil,FALSE)
4. copy formula nomor 3 ke seluruh cells hasil
5. save

Regards,
Kid




On Tue, Jan 5, 2021 at 3:11 PM Adi Abdullah <[hidden email]> wrote:
Dear teman2 milis excel.
Mohon bantuan nya, bagaimana caranya untuk mendapatkan nilai kode PO sesuai dengan kriteria. Sementara kolom yg menjadi acuan nya terdiri dri beberapa kolom.
Mohon bantuan nya, saya coba menggunakan rumus vlookup tapi tidak berhasil. 
Mohon maap jika pertanyaan nya sedikit membingungkan. 
File contoh terlampir.
Sebelumnya saya ucapkan terima kasih.

Best Regards,
Adi 

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAK15MAC3OgaUiT81kCLUwTH8fDMWTu7DY3uL8jcbLyQUVUoECw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPuBoVd%2BbkmDie8D_bXb_L-ZKcGYFcgq-VM5%2BxMK_bjMCw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPsqU1Gv2B6fOUvmcDuonH_aMcenF6X5ZNB6dUXcF1KwXQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Bls: Re: [milis-belajar-excel] Vlookup banyak kolom

Heru Adi
In reply to this post by JAKA KAMIRIN
Dear Mr.Kid dan pak Jaka.
Terimakasih banyak atas jawaban dan bantuan nya :) 
Saya sangat terbantu sekali dengan jawaban dari mr kid dan pak jaka..
Sekali lagi terimakasih pak. Semoga ilmunya berkah :)

Regards,
Adi abdullah 


Pada Sel, 5 Jan 2021 pada 17:03, JAKA KAMIRIN
<[hidden email]> menulis:
Dear Pa  Kid,

Rumusnya sudah berjalan dengan sangat baik,
=VLOOKUP("*|"&M3&"|*";$A$3:$J$19;10;FALSE)

ini rumus A3nya
="|"&B3&"|"&D3&"|"&F3&"|"&H3&"|"

Terima Kasih yaa atas bantuannya

Pada tanggal Sel, 5 Jan 2021 pukul 16.30 Mr. Kid <[hidden email]> menulis:

Pakai vLookUp ya...
1. buat kolom berformula sebelum tabel (misal di kolom A), berisi formula untuk menggabungkan semua key lookup dengan selalu diapit delimiter tertentu
    ="|"&cellKode1&"|"&cellKode2&"|" dst
2. copy formula nomor 1 ka baris seluruh baris data
3. di cells hasil, buat formula vLookUp, dengan nilai yang dicari adalah teks kriteria yang diapit delimiter dan memanfaatkan wildcard chararacter untuk mencari 'yang berisi'
   =vLookUp( "*|"&cellsKriteria&"|*",tabelDataMulaiKolomHasilNomor1,nomor_kolom_yangdiambil,FALSE)
4. copy formula nomor 3 ke seluruh cells hasil
5. save

Regards,
Kid




On Tue, Jan 5, 2021 at 3:11 PM Adi Abdullah <[hidden email]> wrote:
Dear teman2 milis excel.
Mohon bantuan nya, bagaimana caranya untuk mendapatkan nilai kode PO sesuai dengan kriteria. Sementara kolom yg menjadi acuan nya terdiri dri beberapa kolom.
Mohon bantuan nya, saya coba menggunakan rumus vlookup tapi tidak berhasil. 
Mohon maap jika pertanyaan nya sedikit membingungkan. 
File contoh terlampir.
Sebelumnya saya ucapkan terima kasih.

Best Regards,
Adi 

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAK15MAC3OgaUiT81kCLUwTH8fDMWTu7DY3uL8jcbLyQUVUoECw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPuBoVd%2BbkmDie8D_bXb_L-ZKcGYFcgq-VM5%2BxMK_bjMCw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAP5F6bUjkx0OGHaVAFKar2BP5kVszWMdsk7rihfEAz--2kUkdA%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1408594895.7615843.1609898045010%40mail.yahoo.com.