Question: Hi, The formula here works great but I can't figure out how to change it to work with data in columns.

Here is what I have:

=INDEX(A2:E2,SMALL(IF(A1:E1=A3,COLUMN(A1:E1),""),COLUMN()))

A B C D E
1 A B A C D
2 Car Bus Aeroplane Rocket Ship
3 A

I'd expect the result to read:

A B
4 Car Aeroplane

A B
4 #NUM #NUM

This is a question from Using array formula to look up multiple values in a list

Array formula in A:4:

=INDEX(\$A\$2:\$E\$2, SMALL(IF(\$A\$1:\$E\$1=\$A\$3, COLUMN(\$A\$1:\$E\$1)-MIN(COLUMN(\$A\$1:\$E\$1))+1, ""), COLUMNS(\$A:A)) + CTRL + SHIFT + ENTER

copied to the right as far as needed.

Lookup a value in a list and return multiple matches.xls
(Excel 97-2003 Workbook *.xls)

COLUMNS(array) returns the number of columns in an array or a reference

COLUMN(reference) returns the column number of a reference

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text