Results 1 to 6 of 6

Thread: VLOOKUP or INDEX formula for a database search

  1. #1
    Neophyte bdimitrov's Avatar
    Join Date
    Jan 2019
    Posts
    2
    Articles
    0
    Excel Version
    2013

    VLOOKUP or INDEX formula for a database search



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello guys, I am having some trouble with a formula showing two separate results in one cell. I have attached the excel file so you can easily see what I am trying to do.

    I have a lot of physical folders that I have to sort and putnumbers on each one. Then in the excel file you choose thecontent you would like to find from the drop-down menu (for example inC16,C17,C18 "Invoices", "2007", "Mar", respectively) and you getthe number corresponding to the particular folder in another cell (G16).

    The formula I used in cell "G16" works when only one folder number corresponds to the particular contents you have chosen. For example, if you choose "Invoices", "2007", "Mar" you get the number 3 in cell "G16". However, when you change "Mar" to "Apr" for example, you should get both number 4 and number 5 (because there are two folders from April - "Apr A-J", "Apr K-J).

    Does someone know a way in which I could get both numbers to show up in cell "G16"?

    Thank you.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Unless you can guarantee there won't be more than 2 or 3 "folders" for any one choice combo, then it is best to use a custom function.....

    Go to the visual editor (Alt +F11) then Insert|Module and paste this code:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    Back in G16, enter this Array* formula:

    =SUBSTITUTE(TRIM(aconcat(IF(('DON''T MIND THIS SHEET'!H5:H300=SEARCH!C16)*('DON''T MIND THIS SHEET'!J5:J300=SEARCH!C17)*(ISNUMBER(SEARCH(SEARCH!C18,'DON''T MIND THIS SHEET'!K5:K300))),'DON''T MIND THIS SHEET'!G5:G300,"")," "))," ",", ")

    *Confirmed with CTRL+SHIFT+ENTER not just ENTER

    Note: You'll need to save the workbook as a .xlsm file.


  3. #3
    Neophyte bdimitrov's Avatar
    Join Date
    Jan 2019
    Posts
    2
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by nbvc View Post
    unless you can guarantee there won't be more than 2 or 3 "folders" for any one choice combo, then it is best to use a custom function.....

    Go to the visual editor (alt +f11) then insert|module and paste this code:

    Code:
    function aconcat(a as variant, optional sep as string = "") as string
    ' harlan grove, mar 2002
    dim y as variant
    
    if typeof a is range then
    for each y in a.cells
    aconcat = aconcat & y.value & sep
    next y
    elseif isarray(a) then
    for each y in a
    aconcat = aconcat & y & sep
    next y
    else
    aconcat = aconcat & a & sep
    end if
    
    aconcat = left(aconcat, len(aconcat) - len(sep))
    end function
    back in g16, enter this array* formula:

    =substitute(trim(aconcat(if(('don''t mind this sheet'!h5:h300=search!c16)*('don''t mind this sheet'!j5:j300=search!c17)*(isnumber(search(search!c18,'don''t mind this sheet'!k5:k300))),'don''t mind this sheet'!g5:g300,"")," "))," ",", ")

    *confirmed with ctrl+shift+enter not just enter

    note: You'll need to save the workbook as a .xlsm file.
    thank you so much!!

  4. #4
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    20
    Articles
    0
    Excel Version
    2016
    If your excel has "textjoin"

    G16=TEXTJOIN(",",,,IF(ISNUMBER(SEARCH(SEARCH!C16&SEARCH!C17&SEARCH!C18&"*", 'DON''T MIND THIS SHEET'!H5:H300&'DON''T MIND THIS SHEET'!J5:J300&'DON''T MIND THIS SHEET'!K5:K300)),'DON''T MIND THIS SHEET'!G5:G300,""))

    "enter+shift+enter"

    Quote Originally Posted by bdimitrov View Post
    Hello guys, I am having some trouble with a formula showing two separate results in one cell. I have attached the excel file so you can easily see what I am trying to do.

    I have a lot of physical folders that I have to sort and putnumbers on each one. Then in the excel file you choose thecontent you would like to find from the drop-down menu (for example inC16,C17,C18 "Invoices", "2007", "Mar", respectively) and you getthe number corresponding to the particular folder in another cell (G16).

    The formula I used in cell "G16" works when only one folder number corresponds to the particular contents you have chosen. For example, if you choose "Invoices", "2007", "Mar" you get the number 3 in cell "G16". However, when you change "Mar" to "Apr" for example, you should get both number 4 and number 5 (because there are two folders from April - "Apr A-J", "Apr K-J).

    Does someone know a way in which I could get both numbers to show up in cell "G16"?

    Thank you.

  5. #5
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    20
    Articles
    0
    Excel Version
    2016
    option 2 (if you don't have textjoin)

    (helper column in the second worksheet) M14=IF(AND(H14=SEARCH!$C$16,J14=SEARCH!$C$17,ISNUMBER(FIND(SEARCH!$C$18,K14))),'DON''T MIND THIS SHEET'!G14&IFERROR(","&INDEX(G15:$G$300,MATCH(SEARCH!$C$16&SEARCH!$C$17&SEARCH!$C$18&"*",'DON''T MIND THIS SHEET'!H15:$H$300&'DON''T MIND THIS SHEET'!J15:$J$300&'DON''T MIND THIS SHEET'!K15:$K$300,)),""),"") "control+shift+enter"

    (the first worksheet) G15=INDEX('DON''T MIND THIS SHEET'!M:M,MATCH(SEARCH!C16&SEARCH!C17&SEARCH!C18&"*",'DON''T MIND THIS SHEET'!H1:H300&'DON''T MIND THIS SHEET'!J1:J300&'DON''T MIND THIS SHEET'!K1:K300,)) "control+shift+enter"

  6. #6
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    20
    Articles
    0
    Excel Version
    2016
    following is suggestion to formula used in C16:C18 drop downlist

    for C16, change it to following will remove blank cells in dropdown list (similar to C17 and C18)

    =OFFSET('DON''T MIND THIS SHEET'!N$5,,,20-COUNTIF('DON''T MIND THIS SHEET'!N$5:N$24,""),)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •