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!

 

[Aladin Akyurek]

In A1 of deptlookup enter:

=IF(lookup!B2="","",lookup!B2)

In A2 of deptlookup enter:

=COUNTIFS(acct_codes!A2:A20681,"*-"&A$2&"-*")

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

=IF(ROWS($A$3:A3)>A$2,"",INDEX(acct_codes!$A$2:$A$20681,SMALL(IF(ISNUMBER(SEARCH("-"&A$1&"-",acct_codes!$A$2:$A$20681)),ROW(acct_codes!$A$2:$A$20681)-ROW(acct_codes!$A$2)+1),ROWS($A$3:A3))))

 

[ave10]

Thanks so much for your response. The first cell in the deptlookup sheet is A2 (row 1 is a header). So, in A2, I put, =IF(lookup!B2="","",lookup!B2) and I get the value that is in B2 of the lookup sheet and that's it.

In A3, I put: =COUNTIFS(acct_codes!A2:A20681,"*-"&A$2&"-*") and I get 779 as a value. (779 is not a department code).

And in cell A4, I put: =IF(ROWS($A$3:A3)>A$2,"",INDEX(acct_codes!$A$2:$A$20681,SMALL(IF(ISNUMBER(SEARCH("-"&A$1&"-",acct_codes!$A$2:$A$20681)),ROW(acct_codes!$A$2:$A$20681)-ROW(acct_codes!$A$2)+1),ROWS($A$3:A3)))). And I get the 3rd account code listed in the acct_codes sheet. I pressed the CTRL+SHIFT+ENTER after pasting the formula, too.

I may have done this incorrectly but either way, it didn't work. Are there any adjustment I can make?

Thanks again!

 

[Aladin Akyurek]

Why do you have a header in A1 of deptlookup? I mean what is the purpose?

 

[ave10]

A1 says "Account Code" because I want the account codes to go in that column. And B1 says "Description" Because each account code has a description and whatever account code is in column A, column B will have that account codes description.

I have the description part down with a formula of: ​ =IF(A2="","",IF(AND(LEN(A2)=21,COUNTIF(acct_codes!$A:$A,deptlookup!A2)>0),VLOOKUP(JE!C8,acct_codes!$A:$B,2,FALSE),"N/A")&IF(A2=" "," ",VLOOKUP(deptlookup!A2,acct_codes!$A:$B,2,FALSE))) .

I just need the account code part to work.

 

[Aladin Akyurek]

A1 says "Account Code" because I want the account codes to go in that column. And B1 says "Description" Because each account code has a description and whatever account code is in column A, column B will have that account codes description.

I have the description part down with a formula of: ​ =IF(A2="","",IF(AND(LEN(A2)=21,COUNTIF(acct_codes!$A:$A,deptlookup!A2)>0),VLOOKUP(JE!C8,acct_codes!$A:$B,2,FALSE),"N/A")&IF(A2=" "," ",VLOOKUP(deptlookup!A2,acct_codes!$A:$B,2,FALSE))) .

I just need the account code part to work.

In A2 of deptlookup just enter:

=IF(lookup!B2="","",lookup!B2)

In A3 of deptlookup enter:

=COUNTIFS(acct_codes!A2:A20681,"*-"&A$2&"-*")

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

=IF(ROWS($A$4:A4)>A$3,"",INDEX(acct_codes!$A$2:$A$20681,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A$2:$A$20681)),ROW(acct_codes!$A$2:$A$20681)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A4))))

In B4 just enter:

=IF($A4="","",VLOOKUP($A4,acct_codes!$A:$B,2,0))

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel itself puts a pair of { and } around the formula in recognition.

 

[ave10]

Awesome! It seemed like that worked. Thanks a lot! I have one slight question/ adjusted request.

When the user hits "Search" button on the lookup sheet, is it possible to delete whatever department number is in the cell B2? The reason is, in the sheet dept_list, the user can double-click on a department code in Column A of that sheet and that department code populates cell B2 as the search parameter. Upon clicking the "search" button, I'd like whatever department code that was in cell B2 of sheet lookup to be deleted so if the user needs to go back and search for another department, they can double-click the code rather than having to manually delete the value in B2 before choosing another department code.

Thanks again for your help!

 

[Aladin Akyurek]

Awesome! It seemed like that worked. Thanks a lot!

You are welcome.

I have one slight question/ adjusted request.

When the user hits "Search" button on the lookup sheet, is it possible to delete whatever department number is in the cell B2? The reason is, in the sheet dept_list, the user can double-click on a department code in Column A of that sheet and that department code populates cell B2 as the search parameter. Upon clicking the "search" button, I'd like whatever department code that was in cell B2 of sheet lookup to be deleted so if the user needs to go back and search for another department, they can double-click the code rather than having to manually delete the value in B2 before choosing another department code.

Thanks again for your help!

I think that should be done in your macro...

 

[ave10]

I was bale to get that in my macro. Thanks so much!

I have one last question regarding this lookup function. Once the values are listed in deptlookup for column A, I would like the user to be able to double-click on an account code in column A and that would populate the main sheet.

I have code that does that but, it doesn't work on the deptlookup sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Target.Column = 1 Then
    For j = 7 To 447
        If Worksheets("JE").Range("C" & j).Value = "" Then
             Worksheets("JE").Range("C" & j).Value = ActiveCell.Value
             Worksheets("JE").Activate
             Exit For
        End If
     Next j
End If


Application.ScreenUpdating = True


End Sub

Would you be able to help me modify this code so when a user double-clicks an account code it will populate the avaibale cell in column C of the sheet "JE"?

Thanks again!

 

[Joe4]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Column = 1 Then
        Worksheets("JE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Target.Value
    End If

End Sub

 

[ave10]

Hi Joe,

Thanks for your response. It didn't work, however. When I double click the cells on the A column, the cursor just clicks inside the cell to edit it, rather than navigating back to the sheet "JE" and populating the cell in the C column of that sheet with the value I double clicked. Are there any adjustments to the code you reckon I can make? Thanks again!

This is the formula I have in my A column: =IF(ROWS($A$4:A5)>A$3,"",INDEX(acct_codes!$A$2:$A$20681,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A$2:$A$20681)),ROW(acct_codes!$A$2:$A$20681)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A5))))

and this is the code I have in my deptlookup sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    If Target.Column = 1 Then
        Worksheets("JE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Target.Value
    End If


End Sub