Array formula to look up a value and return multiple values in excel
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:
Array formula in B25:
=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell B25 and paste it down as far as needed.
Download excel sample file for this tutorial.
Using array formula to look up multiple values in a list.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article
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
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
Related posts:
- How to return multiple values using vlookup in excel
- Return multiple values if in range in excel
- Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
- Return multiple values if above frequency criterion in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Lookup a value in a list and return multiple matches in excel
- Vlookup with 2 or more lookup criteria and return multiple matches 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/
May 19th, 2010 at 7:57 pm
I used this and it worked great, except for it leaving #NUM! when there is no more data. I plan on having that formula copy and pasted 50 times so that when I add data to my list, it will come up properly. Is there anyway I can get excel to display a zero instead of #NUM! ?
May 19th, 2010 at 8:04 pm
nevermind, I just fixed it with a IF ISERROR thanks anyway though, great example and thanks for posting it
June 27th, 2010 at 11:17 pm
Do you suppose you could post that formula with the IF ISERROR parameters included?
Thanks
June 28th, 2010 at 12:27 am
I figured it out. For those who were having problems with the #NUM value showing, here's the formula with the IF ISERROR parameters included:
=IF(ISERROR(INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))), "", INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))))
This is for the initial formula addressing the initial question, not the subsequent modifications.
June 28th, 2010 at 10:11 pm
Thanks Rich!
Here is a shorter formula removing #num value
Excel 2003
=IF(ROWS($A$1:A1)>SUMPRODUCT(--($B$21=$B$3:$B$16)), "", INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))) + CTRL + SHIFT + ENTER
Excel 2007
=IFERROR(INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))), "") + CTRL + SHIFT + ENTER
July 23rd, 2010 at 6:47 pm
Great formula, and it ALMOST works for me. Unfortunately, I'm getting #num! in every cell. Here's what I'm trying to do: I have data in a1-h10. column c have dates in them. i want to enter a date in a13 and have it check column c for that date and have the entire row of those dates listed in a15-h25. I used your formula but changed the cells and i just get #num!. Here is the formula i used =INDEX($A$1:$H$10, SMALL(IF($A$13=$C$1:$C$10, ROW($C$1:$C$10)-MIN(ROW($C$1:$C$10))+1, ""), ROW()))
Please help. Thank you.
July 23rd, 2010 at 9:11 pm
I also tried using your Extract-all-rows-that-contain-a-value-between-this-and-that-part-21 formula, and in every cell i get #ref!. I don't know what i'm doing wrong. please help.
Josh
July 23rd, 2010 at 10:20 pm
Josh,
I think you are almost there.
Try this array formula in cell A15:
=INDEX($A$1:$H$10, SMALL(IF($A$13=$C$1:$C$10, ROW($C$1:$C$10)-MIN(ROW($C$1:$C$10))+1, ""), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER
Copy cell A15 and paste it into A15:H25.
July 23rd, 2010 at 10:31 pm
They all say #num!? Am i missing a step or formatting?
July 24th, 2010 at 9:04 pm
Josh,
My formula works just fine here.
Maybe your date in A13 can´t be found in your date list in column C?
Check if your date list in column C has another date format than cell A13?
July 26th, 2010 at 8:50 am
I'm using the first date in column C. They all still say #num!? Is it possible you send me an email and I can reply with the excel file as an attachment so you can take a look at it. This formula is the only thing remaining for me to start using this on a much bigger scale in another spreadsheet. It would really help me out. Thank you.
Josh
July 26th, 2010 at 9:42 am
Josh,
You can email me, click "Contact" on the website menu.
July 26th, 2010 at 9:44 am
OK, I'm getting close! i used the formula in your Extract-all-rows-that-contain-a-value-between-this-and-that-part-21. I adjusted it so the 'and that' wasn't a factor and it only took values from 1 cell. I copied it to A15:H25. It populated all of A15:H25, but only with the first row that matched the date. there were 4 other rows that match the date, but it didn't enter them. here is the formula =IF(SUM(IF(($C$1:$C$10=$A$13), 1, 0))=ROW(), "", INDEX($A$1:$H$10, SMALL(IF(($C$1:$C$10=$A$13), ROW($C$1:$C$10), ""), ROW(A1)), COLUMN()))
is there something i need to add or remove?
Thank you again,
Josh
July 26th, 2010 at 11:28 pm
Thank you, Thank you, Thank you. I’m going to add the formula a few comments above to remove the #num! value. Thank you again.
Josh