Help pulling multiple matches (formula repeat?)

**PowerSchoolDude** · 08-21-2009, 03:51 PM

What I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.

=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))

Any help would be great! Thank you.

**JBeaucaire** · 08-21-2009, 04:33 PM

Go ahead and post up a workbook demonstrating your date, your current results, and then manually mockup your desired results somewhere so we can SEE the goal.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**PowerSchoolDude** · 08-21-2009, 04:49 PM

I've added the workbook to show what I have and what I would like.

I also made a mistake on the formula I was using. It's the same except rather than searching through dates, it searches for a type of log entry. I made the change to the original post.

Thanks!

**JBeaucaire** · 08-21-2009, 06:57 PM

This is not possible simply with native functions, at least not with ones I know. What you're doing is called STRING CONCATENTATION, even though your concatenation delimiter is a linefeed, it's still the same thing...concatenating multiple matching criteria into a single cell separated by a delimiter.

Fortunately, there are many string concatenation UDFs out there (User Defined Functions).

I installed Chip Pearsons "StringConcat" UDF into your sheet, then edited it to act the way you want, that is showing datevalues in MM/DD/YY format.

I renamed it DATECONCAT() and here's the code:

Please Login or Register  to view this content.

It's used as
=DATECONCAT(Delimiter,ArrayFormula,ResultRange,NullResult)

The formula I used in C2 was:
=Dateconcat(CHAR(10), IF(INDEX('Paste SFHS'!$A$2:$A$9&'Paste SFHS'!$B$2:$B$9,0)=A2&"08-Wave/Stu. Goals",'Paste SFHS'!$C$2:$C$9, ""))

That's an array formula, so must be confirmed with CTRL-SHIFT-ENTER to activate the array.

Here's your sample sheet back with the UDF in it and working. Notice it is now a .xlsm file, required for the added in UDF.

**PowerSchoolDude** · 08-21-2009, 07:48 PM

You my friend, are a god.

I've spent weeks agonizing over this report and what you gave me was exactly what I needed.

I can't thank you enough for putting that effort to help me out. You are awesome!