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:
A B C D E
1 A B A C D
2 Car Bus Aeroplane Rocket Ship
I'd expect the result to read:
4 Car Aeroplane
...but instead I get
4 #NUM #NUM
Can you offer any advice?
This is a question from Using array formula to look up multiple values in a list
Array formula in A:4:
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
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
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.
Returns the smallest number in a set of values. Ignores logical values and text