Nested Function using MATCH and/or INDEX
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!
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
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?
Try this formula in G1 copied down as far as necessary
confirmed with CTRL+SHIFT+ENTER
Thank you! This works perfectly! You're a genius!
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1