Need help modifying complex row lookup formula

atomec

New member
Joined
Jul 31, 2014
Messages
2
Reaction score
0
Points
0
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

CategoryItemSummary
(of fruits found)
FruitAppleApple
VegCarrotBanana
VegPeasOrange
FruitBanana
FruitOrange


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
 
Essentially you'd need to modify the how the row is returned to the small. using just the range of a10:a16 you are returning 10,11,12,13,14,15, and 16 which will not work. by subtracting the Min of the range and adding back 1 you should now get 1,2,3,4,5,6,7 which is what the small needs. Hopefully that makes sense because I'm not really very good at explaining these things.

=IF(ISERROR(INDEX($A$10:$B$16,SMALL(IF($A$10:$A$16=$E$5,ROW($A$10:$A$16)-MIN(ROW($A$10:$A$16))+1),ROW(A1)),2)),"",INDEX($A$10:$B$16,SMALL(IF($A$10:$A$16=$E$5,ROW($A$10:$A$16)-MIN(ROW($A$10:$A$16))+1,""),ROW(A1)),2))

In my opinion something like =IFERROR(INDEX($B$10:$B$16,SMALL(IF($A$10:$A$16=$E$5,ROW($A$10:$A$16)-ROW($A$10)+1),ROWS($F$12:F12))),"")

is probably a bit more efficient. I remember we briefly discussed this formula before and you were hesitant to go in this direction but hopefully the modification to your original formula gets you going in the right direction.
 
Solved! Thanks so much

Hi Weazel,

Your revised formula works perfectly, and I'm now able to modify the cell reference to apply to any range of data I need. Sorry I doubted you, but when I pasted your original formula (the one you sent last week) into Excel, all I got was a blank cell.

I'm very grateful for your help. This has been driving me nuts!

Andrew

(I'll close off the thread on the other message board as well.)
 
Back
Top