Look up unique range

[KIM Chanthan]

Hi,

Do you have any formula or method to look up unique range?
Please see attached file for my specific question.

Thank for quick reply,
Chanthan

 

[Deepak]

File???

 

[Asheesh]

Hi,

Nice one...

Just put the below in I4 and drag it down..to be acknowledged with CTRL + SHIFT + ENTER


INDEX($C$2:$C$33,MATCH(SMALL(IFERROR(1/(LOOKUP(ROW($A$2:$A$33),ROW($A$2:$A$33)/($A$2:$A$33<>""),$A$2:$A$33)=$G$2)*ROW($C$2:$C$33),""),ROW(A1)),ROW($C$2:$C$33),0))


Attached file for reference

 

[KIM Chanthan]

Hi,

Nice one...

Just put the below in I4 and drag it down..to be acknowledged with CTRL + SHIFT + ENTER


INDEX($C$2:$C$33,MATCH(SMALL(IFERROR(1/(LOOKUP(ROW($A$2:$A$33),ROW($A$2:$A$33)/($A$2:$A$33<>""),$A$2:$A$33)=$G$2)*ROW($C$2:$C$33),""),ROW(A1)),ROW($C$2:$C$33),0))


Attached file for reference

I got it.

Thank again,
Chanthan

 

[Asheesh]

You welcome..but it was a nice one

 

[r1c1]

Good one @Asheesh

Here is another try.

First define two names

start =MATCH($G$2,$A$2:$A$33,0)
end =MATCH(1,INDEX($B$2:$B$33,start+1):$B$33,0)+start

Now select a bunch of cells starting I4 and type,
=INDEX($C$2:$C$33,start):INDEX($C$2:$C$33,end)

and press CTRL+Shift+Enter

You should see the results.

See attached file.

 

[Deepak]

A little sorter version of the same.

CSE

=INDEX($C$1:$C$33,SMALL(IF((LOOKUP(ROW($A$2:$A$33),ROW($A$2:$A$33)/($A$2:$A$33<>""),$A$2:$A$33)=$G$2),ROW($C$2:$C$33),""),ROW(A1)))


A interesting old post for the same.

http://forum.chandoo.org/threads/formula-challenge-021-sumif-in-merged-cells.11927/

 

[Asheesh]

@r1c1 glad you like it

 

[KIM Chanthan]

Good one @Asheesh

Here is another try.

First define two names

start =MATCH($G$2,$A$2:$A$33,0)
end =MATCH(1,INDEX($B$2:$B$33,start+1):$B$33,0)+start

Now select a bunch of cells starting I4 and type,
=INDEX($C$2:$C$33,start):INDEX($C$2:$C$33,end)

and press CTRL+Shift+Enter

You should see the results.

See attached file.

But I don't know why I cannot edit on column I and the formula does not work in other cell. Also, I cannot drag it down too.

Thank

 

[Asheesh]

share your file please..

 

[Deepak]

But I don't know why I cannot edit on column I and the formula does not work in other cell. Also, I cannot drag it down too.

Thank

You haven't understood the @r1c1 replay thus same issue faced. It is a multi cell array formula so you can't simply drag/edit/delete the same.