# Thread: VLOOKUP or INDEX formula for a database search

1. ## VLOOKUP or INDEX formula for a database search

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.

2. 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. Originally Posted by nbvc
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. 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"

Originally Posted by bdimitrov
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. 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. 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
•