Excel Return multiple partial string matches in ar

问题:

Challenge:

• To have a formula (most likely an array formula) that will return multiple partial matches from a column/row.

Parameters:

• Cannot use INDIRECT, as this is not scalable and will break if data is moved or inserted
• Formula must be expandable with minimum effort (ie: drag the corner in the direction you need to expand it to display the next partial match)

Notes:

• Unfortunately the INDEX/MATCH function combination does not work for a partial string match
• I have a solution based on other people's work, which I will place below, but I am also curious if there is an even more elegant solution that I'm not seeing. I would very much appreciate other people's feedback

Demonstration image:

回答1:

In cell A2 (see attached image from question), I've placed this formula:

=IF($E$2="","",IFERROR(INDEX(A$7:A$28,SMALL(IF(ISNUMBER(SEARCH($E$2,A$7:A$28)),ROW(A$7:A$28)-ROW(A$7)+1),ROWS(A$2:A2))),"")) then pressing CTRL+SHIFT+ENTER to enter the formula as an array formula. Finally, dragging this formula down to A5, and then dragging A2:A5 over to the C column.

If a person wants to return their results horizontally instead of vertically (as I have done), they would simply change the ROWS(A$2:A2) portion of the formula to COLUMNS(A$2:A2) and drag sideways the appropriate number of columns.

The original idea for this solution is from barry houdini as an answer to this question.

The way this formula works is:

1. Evaluates if the search term box (E2 for this example) is blank, if it is, it keeps the result cell blank as well. In this example, it is not blank, and it begins calculating.
2. Next it searches for our search term ("ke" for this example) using the SEARCH function. It returns an array of Error messages and (for any matches) numbers of their position in the grid.
3. Next, using the ISNUMBER function, it converts the values in the array to "FALSE" and "TRUE" based on what the SEARCH returned in the previous step. In our example, this cell will be returning a "TRUE" in the Third position of the array.
4. Since we have a "TRUE" response in our IF function, we will now evaluate what to return for that specific spot in the array. The ROW(A$7:A$28)-ROW(A$7)+1 section converts all the absolute row values of our array into relative values based on our array starting location.
5. Inside our SMALL function we now have an array containing "FALSE" and any relative positions of the matches (in this example "3").
6. The next evaluation step is our "nth counter" at the end of our formula. Because we are in the position that would output our first result (A2), it will return the number "1".
7. Our SMALL function evaluates, and returns the first smallest result ("FALSE" is eliminated, and the number returned is the 3 from step 5).
8. Our INDEX function now evaluates, and returns the cell in the third position of the array A$7:A$28 which is "Mickey Mouse".