## Lookup a value in a list and return multiple matches in excel

**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:**

**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

**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

### 9 Responses to “Lookup a value in a list and return multiple matches in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

That works fantastically! Thanks very much. A great way to start on a Monday morning!

i have two colums

department NO

sales 2

computers 1

laptops 1

books 2

i am doing lookup but

getting result is

2 books - i should get "sales" here

2 books

1 laptops - i should get "computers" here

1 laptops

Could you please help me out

rave,

Read this post: How to return multiple values using vlookup

=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

Works great, however if the criteria is not in the table, I need the cell to be blank. E.g ISNA for a vlookup etc excel2003.

Thanks

Ross,

=IFERROR(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)), "")

I'm having trouble with a formula. I need it to look at another sheet within the same workbook, and pull information. My sheet looks like this:

Last Name First Name Grade TCH Status Required Class CH

Thomas John 7 New 12 PHN01 10

It goes on to list 7 more Class and CH columns. Teachers have signed up for classes and I have a spreadsheet with their choices. I want to make sign in sheets but have excel automatically pull the teachers first and last name from the sheet they signed up on. I'm hoping this can be done automatically...:)

Nancy,

Can you explain in greater detail?

I want to make sign in sheets but have excel automatically pull the teachers first and last name from the sheet they signed up onDo you want multiple drop down list containing all the teachers last and first names?

I have a table of dates C32:I82. Each column represents a different type of day off. However, all dates in the range are included in total days off. I want to extract all dates from the range that fall between a start and an end date and store it in another range. I am having great difficulty with this as I am a novice at best.

Wil R.

However, all dates in the range are included in total days offCan you explain in greater detail?