Find multiple strings of data in data set and return values if found

[Peptide]

Hello,
I am looking to see if i can do the following in a formula only. I have a headache from trying so hoping one of you experts can lend a hand i would be grateful. Thanks in advance!

Note
I used cells a,b,c,d,e etc to make the example easy, but I need to be able to use this anywhere in a worksheet. Once I figure it out, I need to be able to cut/paste to other parts of the worksheet.

Logic
Find C1 in data range A1:B5, for each time it finds the value, show the value in column D and E as noted below. When it no longer finds anymore C1, find C2, then C3 etc.

Data Range A1:B5
ABCDE Exact Match 10
BCDEF Exact Match 20
CDEFG Exact Match 40
ABCDE Exact Match 200
CDEFG Exact Match 500

C1=ABCDE
C2=BCDEF
C3=CDEFG
C4=DEFGH

Result looks like this
D1=ABCDE E1=Exact Match 10
D2=ABCDE E2=Exact Match 200
D3=BCDEF E3=Exact Match 20
D4=CDEFG E4=Exact Match 40
D5=CDEFG E5=Exact Match 500

 

[Peptide]

Peter_SSs - Brilliant and this just cut my large file in half!

For my learning, I cant figure out what the 15,6 does in the formula?

 

[Peter_SSs]

15 is the argument for AGGREGATE to act similarly to the SMALL function. 6 makes it ignore error values.

 

[Peptide]

Peter_SSs

thanks for the insight, never used that before, but great to learn and in the end, you REALLY helped a ton on this! Thanks again.

 

[Peter_SSs]

You're welcome.

 

[Peptide]

Hi Peter,
So everything was awesome... then they hit me with this:
The data range can have the exact match, but it could also have additional characters. Is this possible?[FONT=&quot]
[/FONT]
Examples
The ABCDE could start, end or be surrounded by one or many additional characters i.e. HGVHGJHBHABCDEJKNKHLKLKN or HBHJ[FONT=&quot]ABCDE[/FONT]JKNKHLKLKN or [FONT=&quot]ABCDE[/FONT]JKNKHLKLKN or BGJJJHJHBHJ[FONT=&quot]ABCDE

[/FONT][FONT=&quot]
[/FONT][FONT=&quot][/FONT]

 

[Peter_SSs]

The ABCDE could start, end or be surrounded by one or many additional characters

In which column, A or C?

 

[Peptide]

Hi Peter,
It would be A, which the data set that is being searched.


Data Range A1:B5
ABCDE Exact Match 10
BCDEF Exact Match 20
CDEFG Exact Match 40
ABCDE Exact Match 200
HGHBBABCDEHHKHJK Exact Match 200
HBABCDEHJK Exact Match 750

 

[Peptide]

To further clarify if needed

Hi Peter,
It would be A, which the data set that is being searched.

Additional note:
D1 logic is find C1 in A1:B5 and list ABCDE each time it is found, E1 then lists the value found in column B of that data set

So if ABCDE was the example:

D1 to D4 and E1 to E4 result would be:
ABCDE Exact Match 10
ABCDE Exact Match 200
ABCDE Exact Match 200
ABCDE Exact Match 750

Hope this helps?

Data Range A1:B5
ABCDE Exact Match 10
BCDEF Exact Match 20
CDEFG Exact Match 40
ABCDE Exact Match 200
HGHBBABCDEHHKHJK Exact Match 200
HBABCDEHJK Exact Match 750

 

[Peter_SSs]

See if you could work with this.
F1 houses a 1.
D2:F2 copied down to the bottom row of data in column A
Column F could then be hidden if you want.

Excel Workbook
	A	B	C	D	E	F
1						1
2	ABCDE	Exact Match 10	ABCDE	ABCDE	Exact Match 10	3
3	BCDEF	Exact Match 20	CDEFG	ABCDE	Exact Match 200	5
4	ZZZCDEFG	Exact Match 40	DEFGH	CDEFG	Exact Match 40	5
5	XCABCDE	Exact Match 200	XXXXX	CDEFG	Exact Match 500	7
6	2222XXXXX33	aaaa		XXXXX	aaaa
7	XXXXX	bbbb		XXXXX	bbbb
8	CDEFG	Exact Match 500
Peptide 3

 

[Peptide]

Peter - I really can't thank you enough. You have always helped me out on some tough things and they have worked great AND you have always been very patient. Thanks again! Bob