Listing multiple hits horizontally with Excel's FILTER function

If you have ever wondered if there is an easy way to modify VLOOKUP to pull more than one result from an array, note that Excel's new FILTER function can be used to easily return multiple hits.

FILTER can search for when a given value matches another value in the array B:B, and then pull the value from A:A.   In this example, on the worksheet we have a list of last names in column B. There are multiple entries for common last names. The full names are give in column A. For select last names listed in column H, we want to list each result in the A:B array in the columns on the right. The aim is to see if any names listed in column G match those in column A inexactly. With the data arranged in columns J, K, L, etc., we can see at a glance if the names are the same but for the addition of a maiden name, middle initial, abbreviated form of a first name, or some other common change. For example, we can see on row 2 that 'Bob Smith' is a match for 'Robert Smith'.

The FILTER formula pulls the results from the listed array (B:B) when a value on the same row in the second listed array (A:A) matches the value in the cell reference given at the end after the equals sign.

=TRANSPOSE(FILTER(A:A,B:B=H2))

FILTER will return all of the results at once. It's not necessary to use CTRL + D to add them to successive cells.

If you precede FILTER with the TRANSPOSE formula the values will be displayed horizontally automatically.