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).
--
AusTexRich

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

If $D$5:$E$10 is table2 and $D$5:$D$10 (table1) has the values I want to
match, what is A9?
--
AusTexRich


"Ron Rosenfeld" wrote:

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

On Tue, 7 Aug 2007 13:30:03 -0700, AusTexRich
wrote:

If $D$5:$E$10 is table2 and $D$5:$D$10 (table1) has the values I want to
match, what is A9?

The LOOKUP formula only matches one value at a time.

A9 is the value to be matched. It can be some value from Table1.

If you want some other kind of display, you will need to provide more precise
specifications.
--ron