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?
