Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula for looking (finding)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula for looking (finding)
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula for looking (finding)
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula for looking (finding)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
i need help with finding a specific formula | Excel Discussion (Misc queries) | |||
What Excel Formula = finding dup records and removing them? | Excel Worksheet Functions | |||
Finding or creating correct formula | Excel Worksheet Functions | |||
How can i imput a formula in excel for finding the area of a regu. | Excel Discussion (Misc queries) |