How to return next largest observation of text column

[mmrothsc]

Hi, 

Attached is an example file of the column of data I have (Column B) and hardcoded version of data I want (column C), which is also shown below.  If possible, I'd like to do this without an array formula, but SUMPRODUCT with multiple conditions would be fine.  If helpful, my actual spreadsheet also has each of the items separated by " - " under the "Example Data" below in separate columns.

Thanks!

Example Data	Intended Output
AW139 Long Nose - AW139 - 2007 - 2020	AW139 Long Nose - AW139 - 2007 - 2019
AW139 Long Nose - AW139 - 2007 - 2019	AW139 Long Nose - AW139 - 2007 - 2017
AW139 Long Nose - AW139 - 2007 - 2019	AW139 Long Nose - AW139 - 2007 - 2017
AW139 Long Nose - AW139 - 2007 - 2017	AW139 Long Nose - AW139 - 2007 - 2016
AW139 Long Nose - AW139 - 2007 - 2016	AW139 Long Nose - AW139 - 2007 - 2015
AW139 Long Nose - AW139 - 2007 - 2015	AW139 Long Nose - AW139 - 2007 - 2013
AW139 Long Nose - AW139 - 2007 - 2015	AW139 Long Nose - AW139 - 2007 - 2013
AW139 Long Nose - AW139 - 2007 - 2013	AW139 Long Nose - AW139 - 2007 - 2012
AW139 Long Nose - AW139 - 2007 - 2012	AW139 Long Nose - AW139 - 2007 - 2011
AW139 Long Nose - AW139 - 2007 - 2011	AW139 Long Nose - AW139 - 2007 - 2010
AW139 Long Nose - AW139 - 2007 - 2010	AW139 Long Nose - AW139 - 2007 - 2009
AW139 Long Nose - AW139 - 2007 - 2009
AW139 Long Nose - AW139 - 2007 - 2009
AW139 Short Nose - AW139 - 2007 - 2020	AW139 Short Nose - AW139 - 2007 - 2019
AW139 Short Nose - AW139 - 2007 - 2019	AW139 Short Nose - AW139 - 2007 - 2017
AW139 Short Nose - AW139 - 2007 - 2017	AW139 Short Nose - AW139 - 2007 - 2016
AW139 Short Nose - AW139 - 2007 - 2017	AW139 Short Nose - AW139 - 2007 - 2016
AW139 Short Nose - AW139 - 2007 - 2016	AW139 Short Nose - AW139 - 2007 - 2015
AW139 Short Nose - AW139 - 2007 - 2015	AW139 Short Nose - AW139 - 2007 - 2013
AW139 Short Nose - AW139 - 2007 - 2015	AW139 Short Nose - AW139 - 2007 - 2013
AW139 Short Nose - AW139 - 2007 - 2013	AW139 Short Nose - AW139 - 2007 - 2012
AW139 Short Nose - AW139 - 2007 - 2012	AW139 Short Nose - AW139 - 2007 - 2011
AW139 Short Nose - AW139 - 2007 - 2011	AW139 Short Nose - AW139 - 2007 - 2010
AW139 Short Nose - AW139 - 2007 - 2010	AW139 Short Nose - AW139 - 2007 - 2009
AW139 Short Nose - AW139 - 2007 - 2009
AW139 Short Nose - AW139 - 2007 - 2009

[#2]

@mmrothsc Transformed your example data to an Excel table and connected to it with Power Query (=Get & Transform Data, on the Data ribbon). Select "From Sheet" (or From Table/Range on older Excel versions). PQ is built-in in Excel 2016 and later and available as an add-in for older versions.

With a few clicks on some buttons you can create the table like in Column E. See attached. Something worth exploring/learning if you have not used PQ before. No need for complicated formulae. If you have used PQ before, just follow the Applied Steps that I have used.