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

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

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!

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

DaddyLongLegs!

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