If a Cell Contains any one of an array of data, then X. If it contains one of a different array of data, then Y.

[RockNGoalStar]

Hi All,

I could not get the XL2BB to work... maybe it's cos I am on a Mac?

Anyway, I was hoping someone can help me, and I've taken a screenshot to help me explain it...

I have two sales-reps, Adam and Dave. Each of them are assigned a list of Postcode areas; Adam's are in column V, Dave's are in column W.

The postcodes for our individual customers are in column T - as you can see there is a number on there as well, which is not in the rep's lists of postcodes. So if it says TA1, TA3, TA27 in column T, then they would all be covered by the postcode are TA. So some sort of partial text match...?

I would like to be able to add the owner of the Account in column U, ADAM or DAVE, based on whether or not one of their assigned postcode areas appears in the cells in column T.

I really hope that makes sense

Many, many thanks in advance for any ideas - I am a bit of an Excel numpty, but I have tried Googling the topic for a while before posting here...

 

[Anthony47]

Using XL 365 the following formula should work: in U3

=LET(cPC,T3,XList,$V$3:$V$21,YList,$W$3:$W$21,splcPC,MID(cPC,SEQUENCE(LEN(cPC)),1),AcPC, TEXTJOIN("",TRUE,IF(CODE(splcPC)<58,"",splcPC)),IF(COUNTIF(XList,AcPC)>0,"X",IF(COUNTIF(YList,AcPC)>0,"Y","")))

Then copy down

If you need to adapt the formula to your layout:
-cPC is the cell with the raw postcode (T3, in my example)
-XList and YList are the addresses of the two lists (V3:V21 and W3:W21 in my example)

The formula will remove from the original postcodes any and all the numbers; so L2A34 will be trated as LA and LA2b will be trated as LAb (and thus will not match with LA)

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D
1			Adam	Dave
2	TA1	Adam	TA	NW
3	NW10	Dave	LA	SW
4	LA9	Adam	WA	EH
5	SW5	Dave	LS	W
6	WA4	Adam	HP	LS
7	SW6	Dave
8	EH3	Dave
9	LS16	Adam
10	W3	Dave
11	XX12	Not Covered
12	HP13	Adam
13	LS16	Adam
Sheet19

Cell Formulas
Range	Formula
B2:B13	B2	=IF(NOT(ISNA(XMATCH(LEFT(A2,MIN(IFERROR(SEARCH(SEQUENCE(10,1,0),A2),999))-1),$C$2:$C$6,0))),$C$1,IF(NOT(ISNA(XMATCH(LEFT(A2,MIN(IFERROR(SEARCH(SEQUENCE(10,1,0),A2),999))-1),$D$2:$D$6,0))),$D$1,"Not Covered"))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D
1			Adam	Dave
2	TA1	Adam	TA	NW
3	NW10	Dave	LA	SW
4	LA9	Adam	WA	EH
5	SW5	Dave	LS	W
6	WA4	Adam	HP	LS
7	SW6	Dave
8	EH3	Dave
9	LS16	Adam
10	W3	Dave
11	XX12	?
12	HP13	Adam
13	LS16	Adam
14
Data

Cell Formulas
Range	Formula
B2:B13	B2	=LET(a,TEXTBEFORE(A2,SEQUENCE(,10,0)),IF(COUNTIFS(C:C,a),$C$1,IF(COUNTIFS(D:D,a),$D$1,"?")))

 

[RockNGoalStar]

Thank you all so much for taking the time to reply. Much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.