Using VLOOKUP to list values that contain a smaller value

[ave10]

I have account codes that look like this #######-###-##-######. After the first (-) ###, is the department code.
In sheet "acct_codes" all of the full account codes are listed and in sheet "dept_list" all of the departments are listed.

I have a sheet called "lookup", in cell B2 of that sheet the user can enter a department code in cell B2 or double-click a department code in the "dept_list" sheet and it'll populate cell B2. And click a "search" button. Clicking that search button will trigger a macro taking the user to a separate sheet called "deptlookup". I would like for all of the account codes that have the department code being searched for, to be listed in Column A of the "deptlookup" sheet. So, if the user enters '001' in cell B2 and clicks search button, it will take them to the sheet 'deptlookup' and in column A it will list all of the account codes that have the department code '001' inside of it. I tried using a VLOOKUP for it but I believe the logic in my formula is off:

=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), "")

If anyone can help me with this VLOOKUP formula or knows of an easier way to do this, I would be very grateful for any help. Thanks!

 

[Special-K99]

You havent specified clearly enough where the department codes are that are being searched.
I'm assuming Sheet1!column A starting in row 1, change as necessary.

in deptlookup column A

=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(ISNUMBER(SEARCH(lookup!B2,Sheet1!$A$1:$A$1000)),ROW($A$1:$A$1000)),ROW(A1))-(ROW(A$1[)-1),1),"")
Array formula, use Ctrl-Shift-Enter
copy down for as many departments you are searching for

You need to change the references in red to whatever sheet name the departments are in and possibly the row numbers
You'll also need to change the reference in blue to whatever row the departments start on (sheet name is not required)

 

[ave10]

Thanks for your response! The department codes are located in the sheet 'dept_list' in rows A2:A171 and the 'deptlookup' results, after the user searches for a department code, begin in row 2 (A2). Let me know if you need more information for clarity.

I modified the formula you gave me but when I enter it I receive a "There's a problem with this formula..." message. Here is my modified formula:

=IFERROR(INDEX(dept_list!$A$2:$A$171,SMALL(IF(ISNUMBER(SEARCH(lookup!B2,dept_list!$A$2:$A$171)),ROW(deptlookup!A2:A446)),ROW(A2))-(ROW(A$2[)-1),1)],"")

When I hit "Ok" button on the error message prompt, it highlights the red colored part of the formula. Have I modified it incorrectly? Thanks again!

 

[Special-K99]

My fault I think that should be () rather than [)
Apart from that looks good, you've changed exactly all the parts that need changing.

 

[ave10]

Thanks! I changed the [) to () and added an additional [ to close up the ] at the end of the formula. I am getting a different error message, however.

I've tried modifying the formula to be like this: =IFERROR(INDEX(dept_list!$A$2:$A$171,SMALL(IF(ISNUMBER(SEARCH(lookup!B2,dept_list!$A$2:$A$171)),ROW(deptlookup!A2:A446)) ,ROW([A2]))-(ROW([A$2]()-1),1),"") I get the "There's a problem with this formula..." error message

I've also tried changing the formula to this: =IFERROR(INDEX(dept_list!$A$2:$A$171,SMALL(IF(ISNUMBER(SEARCH(lookup!B2,dept_list!$A$2:$A$171)),ROW(deptlookup!A2:A446)) ,[ROW(A2))-(ROW(A$2()-1),1)],"") but I get the message, "The syntax of this name isn't correct. Verify the name: - Starts with a letter ir underscore(_), - doesn't include a s[ace or character that isn't allowed, - Doesn't conflict with an existing name in the workbook.

Do you have any idea if Im missing [] or () or don't have them in the right place? I feel like im so close just missing some small things. Thanks again!

 

[Special-K99]

You have square brackets around the cell reference A2 in the first formula and the same towards the end of the second formula.

Try this

=IFERROR(INDEX(dept_list!$A$2:$A$446,SMALL(IF(ISNUMBER(SEARCH(lookup!B2,dept_list!$A$2:$A$446)),ROW($A$2:$A$446)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter

 

[Aladin Akyurek]

Assuming that you want the list in the lookup sheet, right under B2...

In B3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(dept_list!$A$2:$A$171,SMALL(IF(ISNUMBER(SEARCH(B2,dept_list!$A$2:$A$171)),ROW(dept_list!$A$2:$A$171)-ROW(dept_list!$A$2)+1),ROWS($B$3:B3))),"")

 

[ave10]

Thank you for both responses. However, when I added both formulas, the 3 digit department codes populated column A of the 'deptlookup' sheet. Whereas, I need the full account codes that have the department number being searched for inside of them.

So, for example, if on the sheet 'lookup' in cell B2 (the search cell) I type "150", which is me searching for account codes with the department '150' in it, and I hit the "search" button right next to cell B2, when I get taken to the sheet 'deptlookup' via the macro that was just triggered, starting in A2 on the 'deptlookup' sheet, I would like the full account codes that have 150 in them.
This is what I was hoping the deptlookup sheet would look like:
A2: 1234567-150-89-101112.
A3: 6789102-150-14-098765
A4: 1000000-150-00-123456
and so on.

Right now, starting from cell A2 it just lists the different department codes, not the full account codes that have the department being searched for, inside of it.

The account codes are all listed in the sheet "acct_codes" (A2:A20681) and the departments are are listed out in the "dept_list" sheet (A2:A171)

Thanks again!

 

[Aladin Akyurek]

It's hard to keep track of the sheets involved...

So we have data like this:

A2: 1234567-150-89-101112.
A3: 6789102-150-14-098765
A4: 1000000-150-00-123456

In which sheet?

And apparently we have a destination sheet? How is this called?

Does the destination sheet house 150, apparently a code we need to search for in the data sheet? If so, in which cell of that sheet?

 

[ave10]

So, I think there should really only be four sheets involved.

The sheet "acct_codes" has the list of full account codes. (A2:A20681). Account codes look like this: #######-###-##-#####. (The department code is the section in bold)

The sheet "dept_list" has all of the departments listed (A2:A17)

There is another sheet called "lookup". This sheet is called upon on a button as well. There is a button titled "Lookup" and when the user clicks it, it brings them to the lookup sheet.

Since the account code list is very long this sheet allows the user to search for a specific department, and whatever department code they enter (150 is an example of a department code) in cell B2 of the lookup sheet, after they click the "Search" button next to cell B2, a macro is triggered which takes them to the final sheet "deptlookup". Inside the "deptlookup" in column A, is where I want the VLOOKUP to list all of the full account codes that have the department code that was just searched for.

So, for example, if the department code 150 is searched for in cell B2 of the lookup sheet, when the user clicks "Search" on that page, it will take them to the sheet deptlookup, and in column A of deptlookup, all of the account codes that have the department code 150 in it, will be listed.

I hope that clarified everything. I'm always happy to clarify again if needed. Thanks again!