## Search values distributed horizontally and return corresponding value

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

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

How to use the COLUMNS function

The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]

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.

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Vlookup across multiple sheets

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]

Lookup and return multiple values on the same row from a range excluding blanks

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]

### 9 Responses to “Search values distributed horizontally and return corresponding value”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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?