Search for a substring in cell and return value of

问题:

the following excel tables are given:

Sheet01
String: A
Output: B

+---------------------+--------------+
|       String        |    Output    |
+---------------------+--------------+
| ABC Test01          | It is Test01 |
| DEF Test01          | It is Test01 |
| Test01 GHI          | It is Test01 |
| Hellow Test02 World | Wow Test02   |
| Test02 Sum Sing     | Wow Test02   |
+---------------------+--------------+

Sheet02
Search Criteria: A
OutputThis: B

+-----------------+--------------+
| Search Criteria |  OutputThis  |
+-----------------+--------------+
| Test01          | It is Test01 |
| Test02          | Wow Test02   |
+-----------------+--------------+

So basically I want to find out if Search Criteria in Sheet02 can be found in String in Sheet01.
If so, display in Output (Sheet01) value of OutputThis (Sheet02).

Following works for exact match:

=INDEX(Sheet02!B:B,MATCH(A2,Sheet02!A:A,0))

Now I simply tried to put in the like operator which doesn't make any sense. Because excel can't now what part of String is to be found in Search Criteria.

What I'm looking for is something

=New_Function(SearchCriteriaMatrix, SearchCell, OutputMatrix)

EDIT:

I just used the following code and it somehow works:

=INDEX(Sheet02!B:B,Match(A2,Sheet02!A:A,-1))
The key is "-1". It changes the criteria from an exact match to a broad match. At least that's what I want it to do, but it doesn't workout well. Perhaps someone can use this and help out.

回答1:

Here's a single cell array formula:

1. In cell Sheet01!B2 type:
   =INDEX(Sheet02!B:B,MATCH(1,IF(ISERROR(SEARCH(Sheet02!A:A,A2)),0,1),0))
2. Press ctrl+shift+enter to complete the formula as single cell array formula
3. Copy this cell down to the remaining range Sheet01!B3:B6

Note: It gives the output for the first Sheet02 Search Criteria that matches, and not the first one found in the string. So the string Test02 and Test01 would output It is Test01 because Test01 is listed before Test02 on Sheet02.

回答2:

SEARCH or FIND can find a substring within a string. So something like:

=LOOKUP(2,1/SEARCH(srchCritTbl[Search Criteria],A2),srchCritTbl[OutputThis])

will work.

I made your sheet2 table into a "real" table and I'm using structured references, but it'll work with discrete references also, but the references should encompass only the active part of the data table, and not the entire column.

Here is a screen shot showing the results:

If you were to use direct references to the table on Sheet02, it would look like:

=LOOKUP(2,1/SEARCH(Sheet02!$A$2:$A$3,
Sheet01!$A2),Sheet02!$B$2:$B$3)

The way this works:

• SEARCH returns an array of either a number, or an error, depending on whether or not if finds the contents of table2 within the referenced cell in table 1.
• 1/Search(... will then return either an error, or some number which has to be no greater than 1.
• Using 2 as the lookup criteria in LOOKUP guarantees that it will be greater than any value returned in the lookup_vector.
• If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value. EDIT: As pointed out by @Gregory, this applies if lookup_vector is sorted ascending. When it is not, then the last entry that is less than or equal to lookup_value gets matched.
• since result_vector references the OutputThis column, the matching entry in that column would be returned.

One could also guarantee, in this situation, that lookup_value would be greater than any value in lookup_vector by using a very large number, and eliminating the 1/... portion:

=LOOKUP(9.9E+307,SEARCH(srchCritTbl[Search Criteria],Sheet01!$A2),srchCritTbl[OutputThis])

I used the other form out of habit as it is more generally useful.