I have a sheet in a workbook called lookup. Here, users can enter a department code in cell B2 and click a "Search" button which will lead them to a sheet called deptlookup. deptlookup will list all account codes that have the dept code the user entered inside of it in column A and the account code description in column B. Account codes look like ####### - ### - ## - ###### (bold section is where department code lives in the account code). The formula in column B is working fine, but the formula in column A is not. It is yielding account codes that have different department codes than what the user is searching for. Formulas:
in cell A2: =IF(lookup!B2="","",lookup!B2) (gets dept code).
In cell A3: =COUNTIFS(acct_codes!A:A,"*-"&A$2&"-*") the account codes are located in a sheet called acct_codes a dynamic query refreshes and pulls active acct_codes from a database.
in cell A4: (starting the list of acct_codes) =IF(ROWS($A$4:A4)>A$3,"",INDEX(acct_codes!$A:$A,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A:$A)),ROW(acct_codes!$A:$A)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A4)))) -- doesn't display an account w/ the department code the user searched for.
in cell A5: {=IF(ROWS($A$4:A4)>A$3,"",INDEX(acct_codes!$A:$A,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A:$A)),ROW(acct_codes!$A:$A)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A4))))} Does display account code with department code user searched for.
in cell A6: {=IF(ROWS($A$4:A5)>A$3,"",INDEX(acct_codes!$A:$A,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A:$A)),ROW(acct_codes!$A:$A)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A5))))} (DOES NOT display account with department code being searched for)
in cell A7: {=IF(ROWS($A$4:A7)>A$3,"",INDEX(acct_codes!$A:$A,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A:$A)),ROW(acct_codes!$A:$A)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A7))))} --does display account code with department code user searched for
AND SO ON..
Does anyone know what I am doing wrong? Or have any suggestions/alteration I can make to this formula?
acct_codes!A:Awas specified as a specific range:acct_codes!A2:A206811with this go-around, the account codes in theacct_codesheet is a dynamic query so, idk how long the range will be at any given time, that's why I've tried to go with theacct_codes!A:Aapproach in these equations. Being that the range is larger, A2 looks fine, SO does A3, I believe. Once it gets down to where the account codes are being listed, starting from cell A4 and onward, that is giving me issues. Thanks for any help you can provide.