Using array formula to look up multiple values in a list
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck.
How do you find multiple occurances? Do I need to use match function?
How do i present all the adjacent values to a searchstring?
Answer: If multiple cells match the same criteria you can use this formula E1:E8.
=INDEX(B1:B8,SMALL(IF(A1:A8=D1,ROW(A1:A8),""),ROW())) + Ctrl + Shift + Enter in cell E1:E8. Search value is in D1.
Let us start with this part of formula, which is easy to understand.
IF(A1:A8=D1,ROW(A1:A8),"")
{A,B,C,D,A,C,A,B}=A
{TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE}
{1,5,7}
SMALL(IF(A1:A8=D1,ROW(A1:A8),""),ROW())
{1,5,7}
=INDEX(B1:B8,SMALL(IF(A1:A8=D1,ROW(A1:A8),""),ROW()))
{Car,Rocket,Ship}
Download excel sample file for this tutorial.
using-array-formula-to-look-up-multiple-values-in-a-list.xls
(Excel 97-2003 Workbook *.xls)
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 number in this data set.
ROW(reference)
returns the row number of a reference
Related posts:
- Unique distinct values from multiple columns using array formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
- Filter common values between two ranges using array formula in excel
- Extract largest values from two columns using array formula in excel
- Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
- Filter a column and create a new unique list sorted from A to Z using array formula in excel
- Sort text values by length using array formula in excel
November 20th, 2009 at 12:58 pm
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?
November 20th, 2009 at 1:56 pm
Rob,
In cell A4:
=INDEX($A$2:$E$2, SMALL(IF($A$1:$E$1=$A$3, COLUMN($A$1:$E$1), ""), COLUMN(A:A))) + CTRL + SHIFT + ENTER copied to the right as far as needed.
Thank you for your comment!
November 20th, 2009 at 2:51 pm
Thanks for your reply; again, this works a treat but when I try this with some of my own data in different cells I get an error. I assume this is due to the A:A reference at the end?
I've uploaded a screenshot here: http://tinypic.com/view.php?pic=j9brsk&s=6
November 20th, 2009 at 5:31 pm
This is an array formula, I think you forgot to press Ctrl + Shift + Enter.
November 22nd, 2009 at 11:21 pm
See this blog post: http://www.get-digital-help.com/2009/11/22/lookup-a-value-in-a-list-and-return-multiple-matches-in-excel/
December 8th, 2009 at 11:34 pm
In the top example, with data in vertical columns,is it possible to position the output horizontally (vs. vertically), next to the query?
December 9th, 2009 at 9:05 am
RJW,
Yes, see this blog post: http://www.get-digital-help.com/2009/11/22/lookup-a-value-in-a-list-and-return-multiple-matches-in-excel/