Can MATCH function in an array formula to return multiple matches?
I tried to use the MATCH function in an array formula to return multiple matches (by default it only returns the first match). However, this doesn't seem to work. How can I solve this problem without a complex, unreadable formula?
Answer
How about this, without VBA? [entered on cell C9 as an array formula with CTRL + SHIFT + ENTER, where your searched column is A9:A24, and your search terms are in B1:B4], and dragged down to find multiple hits?
=SMALL(IFERROR(MATCH($B$1:$B$4,$A$9:$A$24,0),""),ROW()-ROW($C$8))
This first uses the array formula to show each 'hit' for any of the search terms matched in the searched column, and then using the Small function with reference to the current cell's row, it returns the earliest hit, then the 2nd hit, then the 3rd hit, etc.
Beyond this point, the reference points to the searched array can be used as needed (converted to the row location of an index function, etc.).
EDIT On further review of the results from this formula, it only returns a single hit for each search term, even if that search term appears multiple times. To resolve this, I first used the formula:
=SMALL(IF($A$9:$A$24=$B$1,ROW($A$9:$A$24),""),ROW()-ROW($E$8))
This shows each hit for a match of the search term found in B1. Here is where I am stuck. I could only figure out how to resolve with the admittedly manual:
=SMALL(IF($A$9:$A$24={"a","b","c"},ROW($A$9:$A$24),""),ROW()-ROW($E$8))
Any suggestions on how to improve to allow multiple hits for multiple terms?
EDIT - Additional option
Okay, I've determined another method of picking up multiple hits. This one relies on considering the location of the previous matches already made. Depending on what you want your result vector to look like (which was never specified by the OP), the results from this are clean but the formula is fairly messy.
The first cell looks like this, in cell H9: =ADDRESS(MIN(IFERROR(MATCH($B$1:$B$4,$A$9:$A$24,0),""))+ROW($A$8),1)
This shows the address of the first cell which matches any of the search terms, using the formula noted further above.
The cell below that (and every cell after that), has this (also an array formula):
=ADDRESS(MIN(IFERROR(MATCH($B$1:$B$4,INDIRECT(ADDRESS(ROW(INDIRECT(H9))+1,1)):$A$25,0),""))+ROW(INDIRECT(H9)),1)
This picks up the address of the cell found in the row above (adding 1 row to avoid re-hitting the same term), and from that new search column from that point to the end point (adding 1 row so that it properly stops at the last ending hit), it re-searches for any of the terms.
This one is again, not that clean [Yes I know there are some improvements I could make to determining what the search should be - either using the text manipulation functions or even doing a relative name reference that changes as you move down the column], but it is automated and, I would argue, cleaner than a VBA module. Especially as, depending on what you want your result vector to be, this could be much simpler.