Matching text in cells

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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