Lookup based on criteria in named range, then concatenate results

[seansanexcel]

we have an interesting formula to figure out.

First, we count the following, where MYDATE needs to fall between the two dates. REGISTER.DT and EXIT.DT are both named ranges A:A and B:B, MYDATE is a named range that points at C1

=SUMPRODUCT(--(REGISTER.DT<=MYDATE)*(EXIT.DT>=MYDATE))

Now for the question. The above formule returns 2 (so it finds two dates)

We now want to find and show these dates in a concatenated string

I have been fiddling with a VBA function StringConcat that I found online, but i doesnt skip zeros (converts all to string), but also doenst understand named ranges.

Anyone have hint? or a Matrix function lookup that could work?

many thanks

 

[GlennUK]

Show the code that you have so far.

 

[seansanexcel]

{=StringConcat((AANM.DT>=DT.START)*(AANM.DT<=DT.EIND)*(TYPE.TXT=I8)*MVAR)}

Where AANM.DT is a named ranges with dates

Where DT.START and DT.EIND are named ranges pointing to 1 cell, a variable

TYPE.TXT and I8 are strings to match, TYPE.TXT is range containing the variables to check I8 against

MVAR is the variable we want to concatenate based on TRUE, FALSES in the previous array criteria filters

Result now is

#VALUE

 

[seansanexcel]

Mayeb the solution lies in something like:
http://www.mrexcel.com/archive/Formulas/3830a.html