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

 

[Peter_SSs]

See if this, using some helper cells, would suffice.
F1 houses a 1
G1 is a stand-alone formula
All other formulas shown are copied down. (F2 only needs to be copied down as far as the list in column C)

Excel Workbook
	A	B	C	D	E	F	G
1	ABCDE	Exact Match 10	ABCDE	ABCDE	Exact Match 10	1	6
2	BCDEF	Exact Match 20	BCDEF	ABCDE	Exact Match 200	3
3	CDEFG	Exact Match 40	CDEFG	BCDEF	Exact Match 20	4
4	ABCDE	Exact Match 200	DEFGH	CDEFG	Exact Match 40	6
5	CDEFG	Exact Match 500		CDEFG	Exact Match 500
6				DEFGH	Not Found
7
Peptide

 

[Peptide]

Peter SS - thanks for the reply - this works fantastic!!

 

[Peter_SSs]

Peter SS - thanks for the reply - this works fantastic!!

Good news, thanks for letting us know.

 

[Peptide]

Hi Peter,
Hate to bug you! One of the things I was looking for was the ability to be able to cut/paste to other areas of a worksheet. I know its putting the $ in the right places but for some reason I am missing something, can you help if you have time?

Thanks in advance!

 

[Peter_SSs]

Not clear to me.
- What, exactly, are you "cutting"?
- Where, exactly, are you cutting it from?
- Where, exactly are you pasting it?

 

[Peptide]

Hi Peter,
Sorry if I was not clear

In this example, I would cut/paste from A1:G6, to anywhere in a worksheet. Could be something like A10:G116 or B10:H16. I will likely expand the as I anticipate the columns B or C to expand with more data. This help?

Thanks again Peter!

 

[Peter_SSs]

The problem is that the G1 formula is referencing a whole column (F) and you are cutting & moving just part of that range (F1:F6)
If you want to cut/paste like that, G1 will need to be like =MAX(F1:F4) where the 4 is the row with the last number if col F (ie the row with the last value in col C)

 

[Peptide]

Hi Peter Peter_SSs,
I am sure this is a very, very long shot, but is it possible that when it doesn't find anything that it comes back as blank?

Meaning in the example on line 6, when DEFGH if not found, the DEFGH and 'Not Found' would not show up and be blank? Another example is if ABCDE (C1) was not found in the data set, it would not show up and the first result would be the BCDEF?

I know its a long shot....

Bob

 

[Peter_SSs]

Try these (better than the last suggestion anyway )

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