mantap, cik gu haps, terimakaseh pakdhe sudarsono, matur tengkyu om ghozi, kamsia ....
ada soalan lagi bagaimana kalo lookup-nya horizontal sudah kucoba 'niru2' cara om ghozi gak bisa pake cara cik gu haps mumetz, gak ngerti ;-) dibantu ya salam YN On Wed, Nov 23, 2011 at 1:24 AM, Haps <hapsari.stlizb...@gmail.com> wrote: > ** > > > array formula multi hasil > =IF(ISNUMBER(SMALL(IF(C7:C11=J7,ROW(1:5)),ROW(1:5))), > OFFSET(D6,SMALL(IF(C7:C11=J7,ROW(1:5)),ROW(1:5)),0),"") > > ARRAY FORMULA MULTI HASIL yang ditulis *sekaligus** di Range G8:G11* > > C-A-R-A-N-Y-A : > 1. Blok / Select Range G8:G11 > 2. Tuliskan rumusnya di Formula Bar > 3. Akhiri dengan menekan Tombol [Ctrl] + [Shift], tahan, lalu tekan > [ENTER] > 4. Lepaskan penekanan 3 tombol > 5. Tarik napas... > > > > > > > On Tue, Nov 22, 2011 at 7:07 PM, yulian nugroho <yulian...@gmail.com>wrote: > >> ** >> >> >> Kawans, >> Saya sudah coba2 otak-atik beberapa kali tapi hasilnya tak betul; >> >> - >> =IF(ISNUMBER(SMALL(IF(C7:C11=J7,ROW(1:5)),ROW(1:5))),OFFSET(D6,SMALL(IF(C7:C11=J7,ROW(1:5)),ROW(1:5)),0),""), >> >> hasilnya : kosong >> - >> =IF(ISNUMBER(SMALL(IF(C7:C11=G7,ROW(1:5)),ROW(1:5))),OFFSET(D6,SMALL(IF(C7:C11=G7,ROW(1:5)),ROW(1:5)),0),""), >> >> hasilnya : hanya bekerja di cell G8 saja >> - >> =IF(ISNUMBER(SMALL(IF(C7:C11=G7,ROW(1:5)),ROW(1:5))),OFFSET(D6,SMALL(IF(C7:C11=G7,ROW(1:5)),ROW(1:5)),0),""), >> >> hasilnya : hanya bekerja di cell G8 saja >> - >> =IF(ISNUMBER(SMALL(IF(C7:C11=G7,ROW(1:5)),ROW(1:5))),OFFSET($D$6,SMALL(IF(C7:C11=G7,ROW(1:5)),ROW(1:5)),0),""), >> >> hasilnya : hanya bekerja di cell G8 saja >> - >> =IF(ISNUMBER(SMALL(IF(C7:C11=G$7,ROW(1:5)),ROW(1:5))),OFFSET($D$6,SMALL(IF(C7:C11=G$7,ROW(1:5)),ROW(1:5)),0),""), >> >> hasilnya : hanya bekerja di cell G8 & G9 saja >> - >> =IF(ISNUMBER(SMALL(IF(C$7:C$11=G$7,ROW(1:5)),ROW(1:5))),OFFSET($D$6,SMALL(IF(C$7:C$11=G$7,ROW(1:5)),ROW(1:5)),0),""), >> hasilnya : hanya bekerja di cell G8 & G9 saja >> >> >> On Tue, Nov 22, 2011 at 6:25 PM, Haps <hapsari.stlizb...@gmail.com>wrote: >> >>> ** >>> >>> biasanya begini >>> array formula multi hasil >>> =IF(ISNUMBER(SMALL(IF(C7:C11=J7,ROW(1:5)),ROW(1:5))), >>> OFFSET(D6,SMALL(IF(C7:C11=J7,ROW(1:5)),ROW(1:5)),0),"") >>> >>> > -- Thanks & Regards Yulian Nugroho
lookup hasil ke-1, ke-2, dst.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet