On Tue, 7 Aug 2007 09:30:07 -0700, AusTexRich
wrote:

Hi,
I have two separate tables. I want to look up a value from table1, match it
to a value that's in table2 and return the corresponding contents of a cell
in table2.

The value I want to match with in table2 is buried with other text in a
single cell.

How do I "find" the value and return the value I want.

For example I want to find this value, 685050-0006 from table1 in the cell
in table2 that has this text, "Test of a part, P/N 685050-0006, Serial
Numbers 0001104 and 0001090" If I get a match then the expression returns,
say, the tech's name. (685050-0006 could be anywhere in the cell).


Use an array formula of the type:

=INDEX($D$5:$E$10,MATCH(FALSE,ISERR(FIND(A9,$D$5:$ D$10)),0),2)

Table2: D5:E10

Lookup value: A9


--ron