Lookup string within list of values

[LEXCERM]

Hi.

I have three values, AB CD EF, in separate columns (col A, B and C)

I want to look these up in a list of strings in column E:
ABTGFR
FVCDXY
YTDREF
ABXYEF

Now here's the difficult bit of trying to explain it!

1) Take AB and look this up in the first two characters only of each string in the list.
2) Take CD and look this up within the 3rd and 4th characters only of each string in the list.
3) Take EF and look this up in the last two characters only of each string in the list.

If the string in the list is ABCDEF then you have a complete match. If the string is, say, ABxxEF, or xxCDEF, or just xxCDxx, then these are partial matches.

I need to identify which strings in the list match any part of AB CD EF in that order.

Easy peasy! :-S

Many thanks in advance.

 

[gaz_chops]

Try
=IFERROR(SEARCH($A$1&"????",E1),0)+IFERROR(SEARCH("??"&$B$1&"??",E1),0)+IFERROR(SEARCH("????"&$C$1,E1),0)

 

[Mackers]

A hacked together method would be something like:

SUM(IF(Left(range, 2)="AB", 1, 0), IF(MID(range,3,2)="CD", 1, 0), IF(RIGHT(range,2)="EF", 1, 0))

Edit: this would return 3 for a complete match, 1 or 2 for a partial match and 0 for no match. If you want it to be more dynamic I could come up with something - do you have any particular requirements?

Second edit: As pointed out below, the MID() should be MID(range,3,2)

 

[gaz_chops]

Makers the mid should be
=SUM(IF(LEFT(E1, 2)="AB", 1, 0), IF(MID(E1,3,2)="CD", 1, 0), IF(RIGHT(E1,2)="EF", 1, 0))

 

[LEXCERM]

Thank you very much for all your feedback. Will try these out today.