Look up last Nth Text value within a column, based on criteria

[mhroberts]

So far, I've done a lot of looking and found the following site:
http://www.experts-exchange.com/Soft...xcel-List.html

There, they describe exactly what I'm trying to do, but in my sheet, the result keeps coming in as a "0".

Here's the setup of my sheet:
B5 - This is where my formula that will pull the last Nth result will be.
E17:E103 - This is where the conditions will live. The condition is statically assigned in B5, "Gained a Status", so if E17:E103 say "Gained a Status" then...
Y17:Y103 - This is where the text value of what I want to look up.

I've played around with it some and here's some things I'm finding:
Done as per that link, result says 0.
If I removed all the "Gained a Status" from E17:E103, then I get an error.
If I change the values in Y17:Y103 to be numeric, I still get a 0 result.

Any advice? I really need to stay away from VBA on this project if possible.

Thanks!

[Marcol]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mhroberts]

Here's a scrubbed down version. Hopefully it will convey the idea.

[mhroberts]

Admins don't hate - but bump!

[benishiryo]

hi mhroberts. try this:

Please Login or Register  to view this content.

also an array formula. the red parts are those i've added

[mhroberts]

Beautiful! Thank you!