Match array of variable strings to the beginning only of a variable length cell

Does this get you close? I put a string in D2 that I want to search for. C3 is the beginning of my list. This will look at C3 and look to see if the first part of it matches D2.

Please Login or Register  to view this content.

Thanks for replying nigelbloomy.

What I'm looking for is a way to compare all variable length strings in the list above (an array) to the beginning only of a variable length cell.
Can your solution do that ?

THIS IS MY LIST OF VARIABLES TO CHECK IF ANY APPEAR AT THE BEGINNING OF THE CELL IF CHECKING

A1 = IP-HAB
A2 = MX-
A3 = TRNS
A4 = PQA-
A5 = IZ-GHS
A6 = CF-DUB
A7 = CR-XC
A8 = QI-NB

B1 = Cell to check
B2 = Cell to check
... and so on

So if either strings in A1, A2, A3, A4, A5, A6, A7 or A8 appear at the beginning ONLY of a cell (i.e. B1 or B2) then return true or some other value in column C.

C1 = Check result
C2 = Check result
... and so on

Hope I did a better job of explaining.

Thanks again!

Does this do what you are looking for?

Please Login or Register  to view this content.

Yes. It takes the cell you are looking at (B1) and creates an array of trimmed down values using all of the lengths of the cells in A1 to A8. Then it compares the values in A1 to A8 with those trimmed down values. Matches will become "TRUE" and those that don't match will become "FALSE." The -- turns the true and false statements to 1 or 0. If at least one match is found, the value will be >0 and the whole formula will return True.