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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  2. Lookup values in a range using two or more criteria and return multiple matches in excel
  3. Lookup two index columns returning multiple matches in excel
  4. Return multiple values if above frequency criterion in excel
  5. Return multiple values if in range in excel
  6. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  7. How to return multiple values using vlookup in excel
  8. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  9. Lookup with multiple criteria and display multiple search results using excel formula
  10. Lookup with multiple criteria and display multiple search results using excel formula, part 3