Return the previous/next different text

[rupertlewis]

Hi there clever excellents!

I apologise in advance for my ignorance and I have tried to source a solution but, fear that it lies in the INDEX/MATCH blackhole of my knowledge!

I have attached a very basic spreadsheet and have manually completed it but, the spreadsheets that I am working on are much bigger and it is simply not feasible to manually complete them.

I am trying to understand the formula I need to be able to return the previous or next name in a string of names based upon an index number that changes. In the spreadsheet, I have two Indices (2428 and 2429 ) and as these change the names will change and therefore the formula needs to restart. The Current name (Column B) is fixed and I want to be able to see who was before this and who was after this ... obviously at the start/end of an index string there will be no one previous/next and this needs to blank or NA.

I hope I have been clear ... and once again apologise for my ignorance.

Many thanks in advance

[XLent]

a few options, one being:

Please Login or Register  to view this content.

the second isn't ideal, from best-practice perspective, but it should do what you need, I think

[rupertlewis]

Thank you, the first one is definitely a good one. The second does not seem to work throughout the column for some reason ... it works with the first set but not the second.

I can definitely work with the "previous" ... thank you!

[XLent]

ok - you may need to post an example to illustrate the failure as, using your sample, the formula should generate your expected results.

[rupertlewis]

Forgive me, I got confused by the side by side names being the same and forgetting that I had Bob before and after (doh) ... it works perfectly; you are a legend. Thanks

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.