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),"")
>>
>>
