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. enter image description here

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.

enter image description here

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.

enter image description here

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:

=IF(lookup!B2=MID(acct_codes!A:A,9,3),VLOOKUP(acct_codes!A:A=MID(acct_codes!A:A,9,3),acct_codes!A:A,1,FALSE), "")

Here is a sample of vlookup with wildcards:

=VLOOKUP("*" & D1 & "*",F1:G4,2,FALSE)

enter image description here


Here is an example of getting multiple returns. Say we start with:

enter image description here

In C1 we enter:


and in C2:


and copy down. Column C marks each row containing 123

Then in D1 enter:


and copy down:

enter image description here

  • Thanks! Would D1 in your example be similar to the cell B2 in my lookup sheet? – anve Jul 13 '17 at 20:21
  • Yes, it's the value to be found in the interior of the account codes. The only wrinkle here is that a VLOOKUP() will only return the first value it finds corresponding to the lookup value. It sounded like you want to provide your users with a list of multiple account codes - every one that contains "123" in this example. Is that right? – Bandersnatch Jul 13 '17 at 20:58
  • @Bandersnatch Yes, that is right. I would like to return all of the account codes that have a particular department code that the user is searching for – anve Jul 14 '17 at 12:50
  • @anve We can use a cascading match & index approach......check back later today – Gary's Student Jul 14 '17 at 12:52
  • @Gary'sStudent great! Sounds good. Thanks so much – anve Jul 14 '17 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.