VLOOKUP or INDEX formula for a database search

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:


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.

nbvc said:

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.

Click to expand...

thank you so much!!

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"

bdimitrov said:

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.

Click to expand...

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"

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