I have the following structure

Column A Column B

Code Account
A1 1000 40001
A2 1000 40002
A3 2000 40003
A4 3000 40004
A5 1000 40005
A6 2000 40006
A7 3000 40007
A8 2000 40008
..................

I need formula, that, when I specify the code, to return all Accounts which
have that code

For ex. in C1 I specify 1000, , then in D1, D2, D3,..to return the
corresponding Accounts 40001, 40002, 40005

Regards

=IF(ISERR(SMALL(IF($A$1:$A$8=$C1,ROW(INDIRECT("1:" &ROWS($B$1:$B$8)))),COLUMNS($A:A))),"",INDEX($B$1: $B$8,SMALL(IF($A$1:$A$8=$C1,ROW(INDIRECT("1:"&ROWS ($B$1:$B$8)))),COLUMNS($A:A))))

"emilija" wrote:

I have the following structure

Column A Column B

Code Account
A1 1000 40001
A2 1000 40002
A3 2000 40003
A4 3000 40004
A5 1000 40005
A6 2000 40006
A7 3000 40007
A8 2000 40008
..................

I need formula, that, when I specify the code, to return all Accounts which
have that code

For ex. in C1 I specify 1000, , then in D1, D2, D3,..to return the
corresponding Accounts 40001, 40002, 40005

Regards

You could filter the list, if all you need to do is see the account numbers.... Use Data /
Filter... / Autofilter

HTH,
Bernie
MS Excel MVP


"emilija" wrote in message ...
I have the following structure

Column A Column B

Code Account
A1 1000 40001
A2 1000 40002
A3 2000 40003
A4 3000 40004
A5 1000 40005
A6 2000 40006
A7 3000 40007
A8 2000 40008
.................

I need formula, that, when I specify the code, to return all Accounts which have that code

For ex. in C1 I specify 1000, , then in D1, D2, D3,..to return the corresponding Accounts 40001,
40002, 40005

Regards

Hi Emilija,

I have a formula for you. Enter this formula at E1

=IF(ISERROR(INDEX(B1,MATCH(D$1,B1:C1,0)-1)),"",INDEX(B1,MATCH(D$1,B1:C1,0)-1))

You need to "array enter" it. At D1 you need to enter the code (say
1000, 2000 etc). Copy down the formula. Wherever it finds 1000 it will
return back the corresponding accounts. Otherwise it will stay blank.
Hope this will work for you. There will be an another formula which
will remove the blank cells if you need it.

Thanks,

Shail



emilija wrote:
I have the following structure

Column A Column B

Code Account
A1 1000 40001
A2 1000 40002
A3 2000 40003
A4 3000 40004
A5 1000 40005
A6 2000 40006
A7 3000 40007
A8 2000 40008
.................

I need formula, that, when I specify the code, to return all Accounts which
have that code

For ex. in C1 I specify 1000, , then in D1, D2, D3,..to return the
corresponding Accounts 40001, 40002, 40005

Regards