Is there any way to use HLOOKUP in an array formula to return multiple matches?

[slabes]

I've been trying entering this into cell c20:

=CONCATENATE(IF(B1:B15=DATE(2006,9,1),A1:A15,""))

or

=INDEX(A1:A15, MATCH(DATE(2006,9,1),B1:B15,0))

to try to return a list of all the matching values in a column. This
only ever gives me the first match. Any ideas??

Michael

 

[Biff]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=COUNTIF(B$1:B$15,"9/1/2006"),INDEX(A$1:A$15,SMALL(IF(B$1:B$15=DATE(2006,9,1),ROW(A$1:A$15)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

 

[slabes]

I'm not following this exactly. I want (if possible) one cell to
display all of the matching values.

M.

 

[slabes]

I'll be more specific about what I'm trying to do. I have a list of
project schedules with several deadline dates columns for each project
on several worksheets, and I would like to know (in a single cell is
possible) what projects all have a print dealine on a specified date,
what projects have a design dealine on a specific date, etc. Make
sense? You solution works (although I'm fighting to get it to
translate across worksheets), but I'd love to have a single cell
display all the matches.

Michael

 

[Kevin Vaughn]

Every time I say that there is absolutely no way you can do this using a
formula, someone comes along and proves me wrong, so I will say, there is
absolutely no way you can do this with a formula. The reason I say this is
that, you wouldn't know ahead of time how many times you would have to
concatenate all of the possible answers together. Actually you probably
could do it if you were willing to use a lot of helper cells and clever
functions, but it is beyond me how you would do so with more than one
possible lookup value. On the other hand, it would probably be relatively
easy to do with a UDF (User Defined Function.) A lot of times people have
provided UDFs when the requirements are clearly defined (though I won't
guarantee you will get an answer.)

 

[Biff]

I want (if possible) one cell to

display all of the matching values.

That is not (practically) possible using the built-in functions available.
You need either a user defined function (VBA code) or you can use the
formula I suggested in a helper column then concatenate those results into a
single cell.

Biff

 

[slabes]

I posted a similar question in the Excel Programming area. Thanks for
pointing me in that direction. I've never used UDF (or coded in VBA)
but am definitely open to the idea.

Michael

 

[slabes]

Thanks. I'm going to try the helper cell. Any thoughts on how to
begin workign this up with VBA?

Michael

 

[Biff]

I don't know enough VBA to be able to do this, sorry.

Biff

 

[Dave Peterson]

The OP has another active thread in .programming.

Subject: Re: new to VBA programming