+ Reply to Thread
Results 1 to 5 of 5

Nested Function using MATCH and/or INDEX

  1. #1
    Registered User
    Join Date
    08-24-2006
    Posts
    48

    Nested Function using MATCH and/or INDEX

    Hello !

    Iíve tried a few different functions, such as VLOOKUP, LOOKUP, MATCH and INDEX and I canít seem to figure out how to do the following:

    Column A will have 30+ text related values (names), i.e., cat, dog, mouse, horse, zebra, etc.

    Column D will have numerical values, for example, 0, 1, 2, 3, 4, etc.

    Hereís what Iíd like to do:

    Iíd like to have a function look down column D, and wherever it sees a value >0, then pick the animal value in the corresponding row in column A, and print this new value found in column G - as it is found.

    So, out of 30 items in column A, if only 12 rows have a corresponding value > 0 in column D, I want to list only those items and in the ORDER THEY WERE FOUND. In other words, I donít want the items sorted by alphabetical, numerical, highest or lowest, etc., I want the new column G to display the items found as they were found, one item in each row. There should only be 12 animal names in column G.

    How would I write this nested function?

    Thank you so much!

    ExcelJunkie

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    well its easiest to write no formulas, have a look at filter and auto filter

    Applying the filter will keep the displayed cells in the order they were typed

    Regards

    Dav

  3. #3
    Registered User
    Join Date
    08-24-2006
    Posts
    48
    Hi Dav:

    Thank you!

    I played with Filter, and Auto Filter, and if I'm seeing things correctly, it requires "manual" adjustment? I need something that automatically updates as new data may be entered into columns A & B (as per first post in this thread). I need that column G to update automatically without any manual intervention, without clicking "sort", or clicking data, filter, advanced filter, etc.

    Any other ideas?

    Thank you!

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Try this formula in G1 copied down as far as necessary

    =IF(ROW()-ROW(G$1)+1>COUNTIF(D$1:D$40,">0"),"",INDEX(A$1:A$40,SMALL(IF(D$1:D$40>0,ROW(D$1:D$40)-ROW(D$1)+1),ROW()-ROW(G$1)+1)))

    confirmed with CTRL+SHIFT+ENTER

  5. #5
    Registered User
    Join Date
    08-24-2006
    Posts
    48

    Thumbs up

    DaddyLongLegs!

    Thank you! This works perfectly! You're a genius!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1