isnumber match (I think)

[ronie85]

Hi, i hope someone can help.

I am trying to search for a persons Initials (AB) which are contained within the current workbook Cell D10. My formula will be inputted into E10.
The range I would like to search this on is in another sheet and range called 'Week 2'!M9:N17

This value will be contained within a cell however with other initials such as AB,BC,DE,FG

I need to formula to search for the first cell occurrence and then return the value within the adjacent cell 'Week 2'!L9:L17
I will then have the same formula within Cell E11 (using Cell D11 as the source) for which i want it to return the second cell occurrence
And then Cell E12 (using Cell D12 as the source) for the third occurrence


I have a similar search formula to find the text within a range of text but this only provides a yes/no so it is hard for me to modify. That formula is =IF(ISNUMBER(MATCH("*"&D7&"*",'Week 2'!K3,0)),"Yes","No") if that setup is of any use for someone to work out my above requirement

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also will D10:D12 always be the same value?

 

[ronie85]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also will D10:D12 always be the same value?

Thanks I will update later when I’m home.

Version not sure of though I’m on Windows 10.

Yes D10:D12 will be the same value for this trial but will change when working to run over many initials, AB CD EF etc

 

[Fluff]

OK, how about

+Fluff 1.xlsm
	D	E	F	G	H	I	J	K	L	M	N
8
9									A	AB, DE,BC	CF, HG,Cd
10	AB	A							B	BA,CC,HG
11	AB	C							C	AD, AB,HH
12	AB	E							D
13									E		GF, AB, CV
14
Main

Cell Formulas
Range	Formula
E10:E12	E10	=INDEX($L$9:$L$17,AGGREGATE(15,6,(ROW($L$9:$L$17)-ROW($L$9)+1)/(ISNUMBER(SEARCH(D10,$M$9:$N$17))),COUNTIFS(D$10:D10,D10)))

 

[ronie85]

OK, how about

+Fluff 1.xlsm
	D	E	F	G	H	I	J	K	L	M	N
8
9									A	AB, DE,BC	CF, HG,Cd
10	AB	A							B	BA,CC,HG
11	AB	C							C	AD, AB,HH
12	AB	E							D
13									E		GF, AB, CV
14
Main

Cell Formulas
Range	Formula
E10:E12	E10	=INDEX($L$9:$L$17,AGGREGATE(15,6,(ROW($L$9:$L$17)-ROW($L$9)+1)/(ISNUMBER(SEARCH(D10,$M$9:$N$17))),COUNTIFS(D$10:D10,D10)))

That works great thanks a alot!!

 

[Fluff]

You're welcome & thanks for the feedback.