Goofy Excel tricks episode #1048577

Note from August 2015: The following method does not appear to work correctly in LibreOffice Calc but it may work, albeit somewhat irregularly, in Google Sheets. Aren't these discrepancies fun?

Just a quick trick I learned today about how to make Excel search a series of values and return one or more corresponding adjacent values for each. I'd normally use VLOOKUP for such a thing but the function only returns the first match it finds. How about situations when I have more than one match, like this one:
A   elephant
B   truck
C   large rock
D   pudding
B   gopher

in which I want to know which values are adjacent to value 'B'?

Googling a bit provided this convenient and non-obvious answer. The general function is:
=INDEX([the values you want to return], SMALL(INDEX(([the search value]=[the values being searched])*(MATCH(ROW([the values being searched]), ROW([the values being searched])))+([the search value]<>[the values being searched])*1048577, 0, 0), COLUMN(A1)))


The function will return one matched value at a time, so to get the other matched values to appear in the same row just copy it over to the adjacent cells in the row. It will return #REF! if the search value isn't found.


Don't forget to make references static as needed, i.e. $A$4:$A$979. The 1048577 is to convert the function from an array function into a normal one, I think. Not really sure what that COLUMN(A1) part is doing. Maybe it's just to establish where the upper-leftmost cell is.


This post is mostly for my own edification but I hope it was helpful. To me. Most people don't use Excel arrays for things that are better suited to a bit of SQL, right?