Lookup range date

  1. 3 bulan yang lalu

    Berikut mas @caton saya sudah mencoba menggunakan rumusnya, tapi hasinya ada yg tidak sesuai. Seperti gambar yg saya lampirkan. Yang muncul hanya 1 data saja.

    nb: saya tdk bisa komen di diskusi sebelumnya.

    Terimakasih.

    Mbak @Lala ...

    Kesalahannya ada pada point ke-3 sebagaimana saya jelaskan di atas:

    [3]. Nama Range mengacu ke range yang salah.

    Coba mbak perhatikan gambar berikut:

    [attachment:5b6eed1c5b2d2]

    Acuan dari nama range tersebut mengacu ke alamat yang salah! Seharusnya alamat-alamat range tersebut mengacu ke sheet LOOKUP DATA dan sheet TEMPLATE seperti terlihat berikut:

    [attachment:5b6eef0a56ae6]

    Coba diubah dahulu... :)

  2. Caton

    Agu 11 Terverifikasi Indonesia + 12.574 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    Mbak @Lala ...

    Saya tidak punya contoh data dengan range tanggal 01/12/2018 s.d. 31/12/2018 seperti yang mbak punya. Sehingga kalau pun saya coba dengan file yang ada pada saya, hasilnya pasti tidak akan muncul. Mengapa tidak lampirkan saja contoh data yang dimaksud?! Jika memungkinkan, simpan berkas ke dalam format XLSB atau XLB guna mengurangi ukuran file sehingga lebih cepat diunggah .... :)

    Saya tidak dapat memastikan bagaimana nilai pada kolom HELPER. Kunci formula untuk sheet LOOKUP DATA ada pada kolom ini. Jika formula pada kolom HELPER tidak menghasilkan nilai padahal ada data yang memenuhi kriteria, kemungkinannya:

    [1]. Ada baris yang tidak memiliki formula, atau tidak seluruh baris terisi formula;

    [2]. Referensi indeks baris yang salah, dimana pada baris data pertama misalkan mulai baris ke 5 (contoh: range P5:AV5), maka indeks baris harus dimulai dari 1 (ROW($A1). Jika saya lihat formula yang ada:

    =IF(SUMPRODUCT(($P5:$AV5>=Lookup.First)*($P5:$AV5<=Lookup.Last));ROW($A3);"")

    ini jelas salah. Karena jika kondisinya benar, hasilnya adalah 3. Pada sheet LOOKUP DATA, indeks pertama yang diambil oleh fungsi SMALL adalah 3. Sedangkan aktualnya, data berada pada indeks baris pertama. Ilustrasinya:

    Shoot01.png

    Bisa dilihat, pada kolom SALAH, fungsi INDEX merujuk ke baris data yang salah. Formula yang seharusnya digunakan adalah:

    =IF(SUMPRODUCT(($P5:$AV5>=Lookup.First)*($P5:$AV5<=Lookup.Last));ROW($A3);"")

    [3]. Nama Range mengacu ke range yang salah.

    Demikian.

  3. Caton

    Agu 11 Terverifikasi Indonesia + 12.574 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    Mbak @Lala ...

    Maaf, saya koreksi sedikit untuk poin ke-2 dari kemungkinan masalah formula pada kolom HELPER. Formula yang seharusnya digunakan adalah:

    =IF(SUMPRODUCT(($P5:$AV5>=Lookup.First)*($P5:$AV5<=Lookup.Last));ROW($A1);"")

    Jadi seharusnya, indeks baris akan dirujuk oleh notasi ROW($A1) untuk baris awal kolom HELPER. Coba lihat kembali formula pada file TES - TEMPLATE PUBLSIH.xlsb pada diskusi sebelumnya. Penjelasan di atas hanya sebatas prediksi saya saja, karena sulit menebak masalahnya dari gambar yang mbak lampirkan. Bisa saja, formula pada kolom HELPER tersebut sudah benar.

    Kemungkinan ke-4: Tipe tanggal pada area DEPARTEMEN (kolom P sampai dengan kolom AV) bukan bertipe numerik, sehingga perbandingan menghasilkan nilai FALSE. Atau bisa juga tanggal acuan pada sheet LOOKUP DATA yang bukan bertipe numerik.

    Saya coba dengan mengubah tanggal sesuai yang mbak maksudkan, dan hasilnya normal saja.

    Shoot02.png

    Demikian.

  4. Saya mencoba di data yang lebih sedkit..
    saya kira rumus sudah sama sperti yang diberikan mas @Caton

  5. Saya coba pakai rumus seperti ini, hasilnya berhasil. tapi ada double data mas @Caton

    =INDEX('Template Publish 2018'!$B$3:$B$14356;SMALL(IF(('Template Publish 2018'!$P$3:$AV$14356>=$G$1)*('Template Publish 2018'!$P$3:$AV$14356<=$H$1);ROW($1:$14347));ROW()-3))

  6. Caton

    Agu 11 Terverifikasi Jawaban Terpilih Indonesia + 12.574 Poin

    Mbak @Lala ...

    Kesalahannya ada pada point ke-3 sebagaimana saya jelaskan di atas:

    [3]. Nama Range mengacu ke range yang salah.

    Coba mbak perhatikan gambar berikut:

    Screenshoot006.png

    Acuan dari nama range tersebut mengacu ke alamat yang salah! Seharusnya alamat-alamat range tersebut mengacu ke sheet LOOKUP DATA dan sheet TEMPLATE seperti terlihat berikut:

    Screenshoot007.png

    Coba diubah dahulu... :)

  7. Oh.. Define name tersebut dibuat terlebih dahulu sebelum membuat rumus, atau otomatis ya mas @Caton ?
    setau saya define name itu dibuat dulu. hehe,
    maaf kalau salah

  8. Caton

    Agu 11 Terverifikasi Indonesia + 12.574 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    Mbak @Lala ...

    Pada file yang saya lampirkan sebelumnya, Defined Named Range itu khan sudah saya definisikan. Pada file yang mbak lampirkan terakhir, nama range Lookup.First, Lookup.Last dan Lookup.Helper itu ada, namun masih mengacu ke alamat yang salah (mungkin karena hasil copy sheet). Nah, coba mbak ubah dahulu acuan (Refers To) dari ketiga nama range tersebut.

    Jika ternyata ketiga nama range tersebut tidak ada, maka pada sheet LOOKUP DATA pasti akan muncul kesalahan (biasanya berupa #REF!). Coba deh diperiksa pada Name Manager (CONTROL F3), lihat apakah ketiga nama range tersebut ada. Periksa alamat acuannya, apakah sudah tepat. Jika belum ada, coba mbak buat kembali nama rangenya... :)

    Untuk formulanya (pada worksheet), untuk sementara jangan dimodifikasi dahulu.

  9. Oh iya stelah saya baca kmbali ????
    Terimakasih atas pnjelasannya, skrg data yg muncul sudah benar ???????????? mas @Caton

  10. Caton

    Agu 11 Terverifikasi Indonesia + 12.574 Poin

    Sama-sama mbak @Lala ... Senang akhirnya mbak sudah menemukan dan menyelesaikan masalahnya ... Semoga bermanfaat ... :)

 

atau Mendaftar untuk ikut berdiskusi!