INDEX Formula that only Indexes cells with a specific text String

[althorian]

Hi,

I have not been able to solve. I need a formula that will convert a column of data into a new column of data minus some repeat lines of garbage text. There are three distinct TextStrings and each repeats after 3 rows and occasionally there is a line or two of garbage.

Example data is in Column H and Result I want is in Column I but I am not sure how to get there.

	COLUMN H	COLUMN I
1	TextString1	TextString1
2	TextString2	TextString2
3	TextString3	TextString3
4	TextString1	TextString1
5	TextString2	TextString2
6	TextString3	TextString3
7	TextString1	TextString1
8	TextString2	TextString2
9	TextString3	TextString3
10	GarbageLine1	TextString1
11	GarbageLine2	TextString2
12	TextString1	TextString3
13	TextString2
14	TextString3

<tbody>
</tbody>

Thanks

 

[AhoyNC]

See if this works for you.
The IFERROR function is only in Excel ver, 2010 and later.
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

Excel Workbook
	H	I
1	TextString1	TextString1
2	TextString2	TextString2
3	TextString3	TextString3
4	TextString1	TextString1
5	TextString2	TextString2
6	TextString3	TextString3
7	TextString1	TextString1
8	TextString2	TextString2
9	TextString3	TextString3
10	GarbageLine1	TextString1
11	GarbageLine2	TextString2
12	TextString1	TextString3
13	TextString2	*
14	TextString3	*
Sheet

 

[althorian]

Thank you for the solution. This worked well!