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
- Using array formula to look up multiple values in a list
- How to return multiple values using vlookup in excel
Related posts:
Vlookup with 2 or more lookup criteria and return multiple matches in excel
Lookup values in a range using two or more criteria and return multiple matches in excel
Lookup two index columns returning multiple matches in excel
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
Recommended categories
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 on
Do 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 off
Can you explain in greater detail?