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
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 copy-paste:
office.microsoft.com/en-ca/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.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 copy-pasted from the website:
And here's my version, modified (I think) to reference the same data in a different location on the worksheet:
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
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 copy-paste:
office.microsoft.com/en-ca/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.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 copy-pasted 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