# 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?