Looking for a value from Bottom to top and skipping possible values

[mkn95u0]

Hello,

This is a little tricky to explain, so i'll do my best. What i'm looking to do, is I have 2 rows of data, I need to return the 'previous' value entered for that item, however if the previous place is say "Home", i want it to skip "Home" and pull the value above it for that specific item.

I attached a vague sample sheet for a reference.

These two columns will be entered by the user:
Column E is a list of fruits
Column G is a list of places

This column I would like to populate with a formula if possible:
Column I - needs to reference the fruit in Column E and return the last value in column G for that fruit, but if that value is "Home", then it needs to pull the next value above for that fruit (and if that is home, it needs to again keep going up until Home is not the answer). The only time Home will be input in I is if the place entered at that time in G is "Home" (so probably made no sense there). - basically if E5 is Apple and the last instance of Apple is E3 and G3 = Ralphs and G5 = Home, then I5 should be Home, but if APple comes up again say E6 and G6 was Walmart, then I want I6 to be Ralphs.

I attached a very vague sample data book, the actual book is going to have thousands of entries over the course of several years and is going to continue going for several more years.

Any help would be greatly appreciated, I do understand i could do this manually, and i've been using a formula that does everything except the skip 'home' part
=IF(E6100="","",IF(COUNTIF($E$306:E6100,E6100)=1,"All Others",LOOKUP(2,1/($E$270:E6099=E6100),$G$270:G6099)))
All this basically does is: if the cell in col. E is blank then leave it blank (this is used for future entries), if the count of Column E where E = Apple is 1, then input "All Others" (my generic entry to indicate the first of it's kind), otherwise look bottom to top "Apple" in E, return the Place in G


Any help would be greatly appreciated.

Sample Data:
sample.xlsx

[Tony Valko]

I didn't download your file.

It sounds like you need to add a test to the LOOKUP function:

=IF(E6100="","",IF(COUNTIF($E$306:E6100,E6100)=1,"All Others",LOOKUP(2,1/(($E$270:E6099=E6100)*($G$270:G6099<>"Home")),$G$270:G6099)))

[mkn95u0]

Thanks! i actually didn't know i could do that, learned something new.

This worked!

[Tony Valko]

You're welcome. Thanks for the feedback!