Lookup based on criteria in named range, then concatenate results

seansanexcel

New Member
Joined
Mar 26, 2012
Messages
3
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

Code:
=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
 

seansanexcel

New Member
Joined
Mar 26, 2012
Messages
3
{=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
Code:
#VALUE
 

Forum statistics

Threads
1,082,478
Messages
5,365,780
Members
400,849
Latest member
candy2052

Some videos you may like

This Week's Hot Topics

Top