VLookup with changeable data

[TrainerJ]

Hi this is difficult to explain.

In Colum A I have a list of workers and in Col B I have a list of titles.

I need a forumla in Col c to look down the list and make sure that the list in Col b meet a certain standard. I.e retunrs yes or no.

The standard is

a word either all, dept or random initals. follwed by a client name, followed by a number, followed by a desctiption.

The problem is the intals change, the client name will change and numbers will change.

I really need to check that all these elemts are in here.

Can you help?

[6StringJazzer]

The one you said is correct is:


ALL Gough & Kelly (W14724 - 5) - E-mail

Your format says:

(Initial, dept or all)SPACE (Clinet name) SPACE(NUMBER)SPACE-SPACE(Decription)


This format is not really parsable. The problem I see is that if the client name has spaces, and the dept or name has spaces, there is no way to tell if they are both there, or only one is there but it has spaces in it. For example

Clay Construction (H24520-1) - E-mail

How can you tell if the dept is Clay and the client is Construction, or whether the client name is Clay Construction and the dept is missing? (Yes, I can look at it and guess, but Excel can't; we need rules that a dumb spreadsheet can follow.)

If there is a strict definition of what dept can be, and what initial can be, this might be feasible.