Sorot misal M21:P21 , buat rumus array formula (caranya , letakkan kursor di
formula bar, ketik rumus di bawah ini ,
lalu tekan [Ctrl] dan [Shift] bersamaan , jangan lepas , lalu tekan
[Enter] )
=INDEX( $D$7:$D$11 , MOD(SMALL(IF(( $C$7:$C$11=$G$7 ),
COUNTIF( $D$7:$D$11 , "<="&$D$7:$D$11 ) *100+ ( ROW( $1:$5 ))),
COLUMN(1:4) ), 100 ))
Sorot misal M22:P22 , buat rumus array formula (caranya , letakkan kursor di
formula bar, ketik rumus di bawah ini ,
lalu tekan [Ctrl] dan [Shift] bersamaan , jangan lepas , lalu tekan
[Enter] )
=INDEX( $D$7:$D$11 , MOD(SMALL(IF(( $C$7:$C$11=$H$7 ),
COUNTIF( $D$7:$D$11 , "<="&$D$7:$D$11 ) *100+ ( ROW( $1:$5 ))),
COLUMN(1:4) ), 100 ))
berubahan terletak row( 1:1 ) menjadi Column( 1:4 ) dan cara penulisan rumus
array
hasilnya :
Buah
anggur
jeruk
pepaya
#NUM!
Hewan
ayam
kucing
#NUM!
To: belajar-excel@yahoogroups.com
From: yulian...@gmail.com
Date: Wed, 23 Nov 2011 18:26:32 +0700
Subject: Re: [belajar-excel] lookup untuk mendapatkan hasil yang ke-1, ke-2, dst
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:G112. Tuliskan rumusnya di Formula
Bar3. 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 beginiarray 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
