Results 1 to 3 of 3

Thread: Need help modifying complex row lookup formula

  1. #1

    Need help modifying complex row lookup formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 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

  2. #2
    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.

  3. #3

    Red face 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.)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •