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

### Category: Vlookup

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(441) Filed in category: Excel, VLOOKUP and return multiple values

Improve you VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]Comments(154) Filed in category: Excel, VLOOKUP and return multiple values

Comments(74) Filed in category: Excel, Vlookup

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.Comments(35) Filed in category: Excel, VLOOKUP and return multiple values

Lookup multiple values in different columns and return a single value

S.Babu asks: Dear Oscar, I m working on the below table. ORDER MODEL MATERIAL QTY STATUS BOM a s6 1 […]Comments(31) Filed in category: Excel, Vlookup

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]Comments(28) Filed in category: Excel, Search/Find, VLOOKUP and return multiple values

Vlookup across multiple sheets in excel

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]Comments(27) Filed in category: Add-in, Excel, Vlookup

Use multiple conditions in Vlookup

Table of contents VLOOKUP and a condition VLOOKUP and a table VLOOKUP - Select a column with a drop down […]Comments(26) Filed in category: Excel, Vlookup

### 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?