I've been struggling with a formula which is turning out to be a lot more complex than I realized.
My goal is to create an array formula that will search for a keyword in one column of text, and if that keyword is encountered, produce a list which summarizes the contents of the cells adjacent to the cells containing the keyword. I realize that's a little confusing. Here's an example that illustrates what I need:
Keyword: Fruit
Category 
Item 
Summary
(of fruits found) 
Fruit 
Apple 
Apple 
Veg 
Carrot 
Banana 
Veg 
Peas 
Orange 
Fruit 
Banana 

Fruit 
Orange 

After much wringing of hands, I found a helpful formula at a Microsoft help page. (As a new user I'm unable to post hyperlinks, so I'll have to give you the address via an old fashioned copypaste:
office.microsoft.com/enca/excelhelp/howtolookupavalueinalistandreturnmultiplecorrespondingvaluesHA001226038.aspx
Anyhow, as I said, the formula I found works beautifully. It's so convoluted, however, that it's way beyond my limited expertise. When I paste the formula into a cell, it works perfectly, assuming all the right columns are populated with data the way the formula expects them to be. But when I try to apply the formula to a different dataset, I can't get it to work, even after painstakingly adjusting the formula to correct all the cell references.
Here's the original (working) formula I copypasted from the website:
Code:
{=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))}
And here's my version, modified (I think) to reference the same data in a different location on the worksheet:
Code:
{=IF(ISERROR(INDEX($A$10:$B$16,SMALL(IF($A$10:$A$16=$E$5,ROW($A$10:$A$16)),ROW(10:10)),2)),"",INDEX($A$10:$B$16,SMALL(IF($A$10:$A$16=$E$5,ROW($A$10:$A$16)),ROW(10:10)),2))}
I've tried everything I can think of, but can't find a formula that works on any dataset other than the one in the tutorial. I realize I'm in way over my head, but I think if I could just see the same example extrapolated to different rows, I could figure out where I'm going off the rails. Can anyone fix my formula and/or offer a suggestion as to where I'm going astray?
I've also created a workbook that illustrates my problem. Again, as I'm unable to post attachments or hyperlinks, I've uploaded it to Dropbox and I'll paste the location below:
https://dl.dropboxusercontent.com/u/73471043/row%20lookup%20issue.xlsx
Thanks!
Andrew
Bookmarks