Extracting a phrase from a dynamic column.

[gsdanger]

Good Morning gurus of this forum.

I have used your expertise in the past with very pleasing results. You guys are the best!
Apologies for the inputted version of my spreadsheet. I am unable to follow the instructions for using the add-in option mentioned. Hope you don't mind.
I now have another scenario that I just cannot work out (I'm still a numpty!), and am hoping you can assist me with a solution.
In cell E10, I have the following formula: =INDEX($G$1:$H$16, MATCH(C2, $G$1:$G$16,0),LEFT($H$1:$H$16,FIND(".",$H$1:$H$16)-1)).

A B C D E F G H

1		Nr.				Nr.	The Database Area…
2		1				1	2/10
3		2					3/12
4		3					1/11
5							In Equal Class. Ran very well last start and is a big chance in this race.
6						2	2/11
7							4/17
8							5/11
9			The Results				6/11
10			1	#VALUE!			4/9
11			2	#VALUE!			3/10
12			3	#VALUE!			In weaker class. Did well last start and a repeat of that effort will see him in the money.
13						3	9/11
14							5/13
15							3/7
16							In stronger class. Will need to improve on the current form to have any chance in this race.
				I'm looking to extract the three below phrases from h1:h16, and place the matched phrase in cell references c10 to c12.
				In Equal Class.
				In weaker class.
				In stronger class.
				Do I use the INDEX, MATCH, LEN, FIND and LEFT functions to achieve this?

Naturally, it's not working. Please give me some direction in regards to the correct formulas to use to achieve the extraction of the phrases.
As you can see, the H column is dynamic. That is, the number of rows pertaining to each individual number (1,2,3 in this case) will be different depending
on the number of rows within each number's record.
Thank you, in anticipation of your assistance.

Kind Regards......gsdanger

 

[jtakw]

Good day Stephen,
Thank you for your prompt reply.
I have input your code into my spreadsheet, however it does not extract anything into the cells I specified.
The code runs through and responds with the "" at the end. Can you please have a look at the code, to see if it works correctly.
Much appreciated Stephen...
Thank You....regards...gsdanger

Hi,

I don't have Office 365, and neither Excel 2019, I'm on Excel 2016, so I'm not sure.
Try the formula(s) @StephenCrump provided, and Array Enter, meaning CSE (control, shift, enter), and see if that works for you.

 

[StephenCrump]

Array Enter, meaning CSE (control, shift, enter), and see if that works for you.

Thanks @jtakw, that should do it.

Clearly I can't be trusted with SMALL()! @Peter_SSs picked up the same omission earlier today in Post#16 here: How to solve this problem? is it array?

 

[gsdanger]

Hi to everyone who responded to my request.
As you know, I am a complete novice with regards to using complex excel formulas.
I appreciate the efforts of all of you to educate and assist me with this problem.
bebo02199 and Stephen's solution worked in the sample spreadsheet I imported into this forum.
I have tried to transfer the formula to my actual spreadsheet (substituting the relevant column and row addresses, where required - which is all of them).
Some of the formula terms are confusing (aggregate? and more) but I still get a blank response (the last quotation marks "" take effect).

Again, I thank you all for your efforts and inputs, however, I will close this request, as I do not know where to go to from here. If the formulas you guys have given me cannot assist me (this is my fault, as your solutions work within the sample spreadsheet I supplied), then I give up and look to do something else.

Once again, to all of you, thank you for your time, efforts, education, and above all, your patience.

Kind Regards....gsdanger