I have long account codes separated by dashes so they look like ##### - ### - ## - ######. One of these dashes that separate the numbers is a departmental code. In my workbook I have a sheet called "dept_list" and in column E of that sheet I have a formula that derives the department code, in columns F & G, I have the account code & it's description.
The list of account codes is very long so in order to save time, I have a sheet called "lookup" where the user can look up codes based on the department code. So, for example, if the account code the user is looking for has a department code '123' inside of it, they can type '123' in cell B2 on the 'lookup' sheet and click a 'search' button next to the cell which takes them to a separate sheet called 'deptlookup' which will list all of the account codes that have the dept code '123' inside of it, as well as the account description.
I think a VLOOKUP might work for this. I'm just not sure how to set it up syntactically or if I have to do this in VBA. I've never done this before so, if anyone can give me any solid suggestions or advice on the best way to go about doing this, I would really appreciate it. Thanks!
This is just two rows of the "dept_list" sheet. Column A is the list of dept codes, Column B is the dept description. Column E is a formula "=MID(F cell,9,3)" which derives the dept code embedded in the full account code and column F is the account code and G is the account description.
This is the search row in sheet "lookup". In cell B2 is where I'd like the user to enter a dept code like '000' or '001' or '002', etc. and click the "search" button.
After clicking the "search" button the user will be taken to a sheet called "deptlookup" where I'd like all of the full account codes and the account description that contain the dept code that was searched for to be listed here. The reason the description has a #N/A is because I have a VLOOKUP in those cells to pull the description based on the account code populated in Column A.
I have tried to use a VLOOKUP based on the lookup sheets search parameter cell. I am essentially trying to say: IF the value in lookup sheet cell B2 = MID(Column A of sheet acct_codes,9,3), use VLOOKUP to pull all of those account codes that match that dept code, if not then return nothing.
=IF(lookup!B2=MID(acct_codes!A:A,9,3),VLOOKUP(A2, acct_codes!$A:$A,1,FALSE), " ")
I also tried this but it still doesn't work: