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

...but instead I get

A B
4 #NUM #NUM

Can you offer any advice?

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

Answer:

Lookup a value in a list and return multiple matches

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.

Download excel file for this tutorial.

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

Functions in this article:

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

Related articles