Aladin - Array Formula

Info

Malc --

Not sure I understand the situation.

A1:A100 houses string values.
D1:D10 houses also a set of string values.
It seems you have numerical values in E1:E10 which correspond rowwise to the string values in D1:D10. Right?
You want a formula in B2 that should return what?
I've difficulty in untangling specifically:

"E.g. Cell A2 contains "Trace enquirey via intranet" and D5 contains "TRACE" E5 contains "D5". I want a formula to return D5 to B2."

Care to elaborate a bit more?

Aladin

Re: Info

Yes
Yes and the string values are one word
Alphanumerical
The alphanumarical contents the cell in col E in the row of the cell that found a matching word in the A1:A100's phrases
See comments amongst yours

perhaps below helps
If this phrase contains a word (A2) that is in this list(D5) them return the code for the word (from E5). i think the find or search function fits into it somewhere but it'll need to scroll through D1:D10 checking to see if there is a match.

For each cell in range A1:A100 scroll through the words in D1:D10, looking for the same word in A?, If the word is found get the contents of E? and return it to A? otherwise move onto the next cell in Col and scroll through D1:D10

An attempt

Malc --

I took what follows to be the layout of your data:

={"Trace inquiry via intranet","code1","","TRACE","code1";
"Email to client","code2","","EMAIL","code2";
"Trace by fax","code1","","WEB","code3"}

A1:A3 houses the long strings for which a code from column E must be retrieved.
B1:B3 shows the results of the formulas in B (see below).
C is empty.
D1:D3 houses the relevant single words like TRACE.
E1:E3 houses the codes associated with the words in D1:D3.

Now the formula that I used in B1 to retrieve a code that applies to A1:

=VLOOKUP(INDIRECT(ADDRESS(SUMPRODUCT((ISNUMBER(SEARCH($D$1:$D$2,A1)))*(ROW($D$1:$D$2))),4)),$D$1:$E$2,2,0)

which I copied down till the last row of data.

The number 4 as argument to ADDRESS stands for column D where single words are.

If there is no D-word in an A-entry, you'll get #N/A for that A-entry.

Am I close to what you wanted?

Aladin

=========== and the string values are one word

Thanks I'll try it tomorrow

and the string values are one word