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?
Answer:
Array formula in B25:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Copy cell B25 and paste it down as far as needed.
Remove #NUM errors
Excel 2007:
Excel 2003:
Explaining array formula in cell 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)))
Step 1 - Find matching cells using a comparison operator
=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)))
= (equal sign) is a comparison operator and checks if criterion ($B$21) is equal to values in array ($B$3:$B$16). This operator is not case sensitive.
$B$21=$B$3:$B$16
becomes
Maine = {"Kansas"; "Maine"; "South Dakota"; "Montana"; "Delaware"; "Kentucky"; "South Carolina"; "South Dakota"; "Oregon"; "Maine"; "Oregon"; "Delaware"; "Maine"; "Kansas"}
and returns
{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}
Step 2 - Convert boolean values to row numbers
=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)))
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
IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-MIN({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16})+1, "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-3+1, "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-2, "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}, "")
and returns
{""; 2; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; 13; ""}
Step 3 - Return the k-th smallest number
=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)))
SMALL(array, k) returns the k-th smallest number in this data set
SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))
becomes
SMALL({""; 2; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; 13; ""}, ROW(A1))
becomes
SMALL({""; 2; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; 13; ""}, 1)
returns 2.
Step 4 - Return a value or reference of the cell at the intersection of a particular row and column
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.
=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)))
becomes
=INDEX($C$3:$C$16, 2)
becomes
=INDEX({"Smith"; "Johnson"; "Williams"; "Jones"; "Brown"; "Davis"; "Miller"; "Wilson"; "Moore"; "Taylor"; "Anderson"; "Thomas"; "Lopez"; "Larsen"}, 2)
and returns Johnson in cell B25.
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.
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
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
Recommended articles
Check out these posts and learn more about vlookup.
- How to return multiple values using vlookup
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Using array formula to look up multiple values in a list
- Search for multiple text strings in multiple cells in excel
- Vlookup – Return multiple unique distinct values in excel
- Fuzzy vlookup (excel array formula)
- Lookup using two criteria in excel
- Search case sensitive and return multiple values in excel
- Vlookup a range in excel
- Vlookup with multiple matches returns a different value in excel
- Vlookup of three columns to pull a single record
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
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
November 3rd, 2010 at 12:28 am
Oscar,
Recently found your site and find the examples and information absolutely wonderful. Wish I had found your site earlier. I am currently designing a spreadsheet and require to do a lookup where I am matching two values and displaying a third. I am required to look up a Sales persons name and discount rate used for particular clients and return the clients Account number.
This is the formula I have come up with so far =INDEX($M$3:$M$14, SMALL(IF(AND($P$5=$K$3:$K$14,$P$4=$L$3:$L$14), ROW($K$3:$K$14)-MIN(ROW($K$3:$K$14))+1, ""), ROW(A1)))
This is only a little test example I have been working on the main spreadsheet is a lot larger with the values spread apart by 20 or more columns. Any suggestions you have would be greatly appreciated.
Thanks in advance
November 3rd, 2010 at 10:30 am
Scott Everist,
=INDEX($M$3:$M$14, SMALL(IF(($P$5=$K$3:$K$14)*($P$4=$L$3:$L$14), ROW($K$3:$K$14)-MIN(ROW($K$3:$K$14))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER.
December 7th, 2010 at 12:53 pm
hi,
i have two workook...1st workbook is called the master sheet and the second is new workbook where in i had to filter out the data that i require in the new workbook and then i have to copy and paste into the master sheet that is the 1st workbook..could u please suggest me
December 7th, 2010 at 1:40 pm
Can you describe your problem in greater detail?
January 4th, 2011 at 5:18 pm
Oscar:
So thankful to have found this formula. I modified it slightly so that results are provided horizontally in the same row rather than vertically. The formula works great in the 1st row, but when I try to copy it down to subsequent rows, it keeps giving me the same output as the 1st row. Can you help? Here is the formula I'm using:
{=INDEX(Results!$B$1:$B$4372, SMALL(IF(Results!$A$1:$A$4372=$A$1, ROW(Results!$A$1:$A$4372)-MIN(ROW(Results!$A$1:$A$4372))+1, ""), COLUMNS($A:A)))}
January 4th, 2011 at 8:56 pm
Greg,
True, all matching values are returned (Results!$A$1:$A$4372=$A$1) horizontally.
When you copy your formula to the next row, nothing changes. The same values are returned.
What values are you looking for, in row two?
Values filtered with another criterion, Results!$A$1:$A$4372=$A$1?
Or adjacent values to Results!$B$1:$B$4372?
January 4th, 2011 at 9:20 pm
I believe values filtered with another criterion - the criterion in A2, then A3, then A4...with all matches for A2 placed in B2, all matches for A3 placed in B3...
The data sheet ("Results") I'm pulling from has 2 columns:
ART101 The professor was great!
ART101 There was too much work in this class.
ART101 Learned a lot.
ART333 Good class.
ART333 Loved the lectures.
The sheet I'm trying to create: all comments for a given course placed in the same (1) row, each comment in a new column. So...
ART101 The professor was great! There was too much work...
ART333 Good class. Loved the lectures.
January 4th, 2011 at 9:25 pm
Greg,
Try =INDEX(Results!$B$1:$B$4372, SMALL(IF(Results!$A$1:$A$4372=$A1, ROW(Results!$A$1:$A$4372)-MIN(ROW(Results!$A$1:$A$4372))+1, ""), COLUMNS($A:A))) + CTRL + SHIFT + ENTER
January 4th, 2011 at 10:29 pm
Presto! Wish I had found you earlier & saved so many hours! Thanks so much for lending your expertise.
Greg
January 5th, 2011 at 1:36 am
=INDEX(Sheet1!$B$2:$B$30102,SMALL(IF((Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102<=$Q$2),ROW(Sheet1!$B$2:$B$30102)),ROWS($1:1)))
Where
B=Region
L=Target Start Date (ranging from early 2005 and still growing)
Q1&Q2 are start/end dates to narrow the field for L
The problem i am finding is that many of our earlier entries (mostly from 2005-2008, but found even in january 2011) do not have a target start date. With that said, I am unable to narrow my search by start/end date (q1/q2) b/c the query returns all entries w/o target start dates first (1/0/1900...there are thousands of these that I DO NOT NEED).
How can i modify this query to eliminate blank cells or ONLY return the dates that i specify (q1/q2)?
Thanks in advance!!!
January 5th, 2011 at 9:15 am
Don,
=INDEX(Sheet1!$B$2:$B$30102,SMALL(IF((Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102< =$Q$2),ROW(Sheet1!$B$2:$B$30102)),ROWS($1:1)))
ROW(Sheet1!$B$2:$B$30102)
returns this array:
(2, 3, 4, 5, ... , 30102)
I think you want it to return this array:
(1, 2, 3, 4, ... , 30101)
So the formula becomes:
=INDEX(Sheet1!$B$2:$B$30102,SMALL(IF((Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102< =$Q$2),ROW(Sheet1!$B$2:$B$30102)-MIN(ROW(Sheet1!$B$2:$B$30102))+1),ROWS($1:1)))
or this:
=INDEX(Sheet1!$B$2:$B$30102,SMALL(IF((Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102< =$Q$2),ROW(Sheet1!$B$1:$B$30101)),ROWS($1:1)))
So why use: ROW(Sheet1!$B$2:$B$30102)-MIN(ROW(Sheet1!$B$2:$B$30102))+1
If you have named ranges instead of absolute cell references, the formula automatically adjusts to whatever cell range you select in the "Name Manager".
Now, you question.
Try this formula:
=INDEX(Sheet1!$B$2:$B$30102, SMALL(IF((Sheet1!$L$2:$L$30102<>"")*(Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102<=$Q$2), ROW(Sheet1!$B$2:$B$30102)-MIN(ROW(Sheet1!$B$2:$B$30102))+1), ROWS($1:1)))
January 5th, 2011 at 5:01 pm
I definitely underdstand where my logic was flawed with the original formula. Thanks for the correction!!!
Unfortunately, the new formula:
=INDEX(Sheet1!$B$2:$B$30102, SMALL(IF((Sheet1!$L$2:$L$30102"")*(Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102<=$Q$2), ROW(Sheet1!$B$2:$B$30102)-MIN(ROW(Sheet1!$B$2:$B$30102))+1), ROWS($1:1)))
is still yielding blank cells (which will always occur due to departmental error). I need to accurately account for data between certain date ranges going back as far as 20005, but am unable due to these blank cells (which are contracts that might or might not be billed at a later date, so removal is not a possibility).
Do you have any other suggestions how I can avoid having blank cells return within this query?
Thanks in advance for your help, you are a lifesaver!!!!
January 5th, 2011 at 5:22 pm
I copied down the formula wrong in my last reply, but the formula:
=INDEX(Sheet1!$B$2:$B$30102, SMALL(IF((Sheet1!$L$2:$L$30102"")*(Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102<=$Q$2), ROW(Sheet1!$B$2:$B$30102)-MIN(ROW(Sheet1!$B$2:$B$30102))+1), ROWS($1:1)))
does still query blank cells. Sorry for the confusion.
January 5th, 2011 at 5:24 pm
It isn't allowing the two arrows to copy over from my computer to this thread (the ones after the first $L30102, where i assume you are trying to avoid the blank cells). Sorry for the multiple responses, just want to avoid confusion.
January 5th, 2011 at 6:40 pm
Don,
I recreated your problem and tried my formula. It works here. Are you sure the empty cells in Sheet1!$L$2:$L$30102 are empty?
January 5th, 2011 at 7:19 pm
yes, they are empty (as far as i can tell) and yield a 1/0/1900 result when returned through a query.
The problem might be that "Sheet1!" is pulling data from an Access database directly. Could this cause a problem? I can't alter the access data as it is utilized by various departments within my company, which is why i had the data pushed to the excel spreadsheet (i can refresh the data as often as i like).
January 7th, 2011 at 5:28 pm
Is there any way to modify this formula to specify dates w/in the formula, instead of using specified cells? I say this b/c when i use a sumproduct formula
=SUMPRODUCT((Sheet1!$L$2:$L$30072>=DATE(2011,1,15))*(Sheet1!$L$2:$L$30072<=DATE(2011,1,21))*((Sheet1!$B$2:$B$30072=$A$20)*(Sheet1!$AI$2:$AI$30072=$A23)))
it, obviously doesn't return any 1/0/1900......can we do this with an index? Or can you think of another way to avoid the 1/0/1900 error?
January 7th, 2011 at 10:14 pm
Don,
If I format an empty cell as Date, the cell returns nothing.
If I format an cell containing 0 as Date, the cell returns 1-0-1900.
Try this formula:
=INDEX(Sheet1!$B$2:$B$30102, SMALL(IF((Sheet1!$L$2:$L$30102<>0)*(Sheet1!$L$2:$L$30102>=$Q$1)*
(Sheet1!$L$2:$L$30102<=$Q$2), ROW(Sheet1!$B$2:$B$30102)-MIN(ROW(Sheet1!$B$2:$B$30102))+1), ROWS($1:1)))
February 25th, 2011 at 7:57 pm
Hi Oscar et al, need your help, I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there prizes daily (1 week). take note that in col. A fruits name may randomly repeated in col A. What I need is put in Sheet 2 col A all fruit name but not repeated and put to column B to H, I to N, O to U there prizes .see sample below. hope u understand.
A B C .... I
1 apple 10 11 .... 8
2 orange 9 9 ..... 10
3 apple 11 11 ..... 12
4 apple 14 10 ..... 10
5 grapes 15 15 ..... 14
In sheet 2 answer should be like this.
A B C ..... H I J.....N O P.... U
1 apple 10 11 8 11 11 12 14 10 10
2 orange 9 9 10
3 grapes 15 15 14
tHANKS
February 25th, 2011 at 8:02 pm
this was like returning multiple values, but in a columns not in a row....
February 27th, 2011 at 10:51 am
john,
read this post: Merge matching rows in excel
February 27th, 2011 at 6:04 pm
Hi Oscar, that's was great, but one thing I'd noticed, it was arranged ascending, i think due to =small(), i'd tried to omit small but it failed on the second batch of lookup value e.g. apple.
...
by the way, i used this formula to get unique values on col A.
=if(countif($A$2:A2,A2)=A2,""), then I copy all return values except blank after filtering,
February 27th, 2011 at 9:28 pm
john,
Yes, it is arranged ascending. The array formula would be complicated and large if I had implemented all your requirements. I think it is much easier to create an user defined function to solve your question.
February 28th, 2011 at 9:36 pm
john,
I have uploaded a new excel file to blog post: Merge matching rows in excel
Sheet 3 contains values, not arranged. The downside is the array formula is a lot more complicated. I created named ranges to minimize formula size.
March 1st, 2011 at 5:01 am
A big thanks Oscar, I'd downloaded it and will try to understand that..you're awesome...keep up...
March 30th, 2011 at 8:53 pm
Hi,
I understand the formula very well. The only issue im having is that when I press Ctrl-shift-enter, the array works but the row(a1) part and any variation of it doesnt change. Meaning that instead of becoming a1,a2,a3 ... it is a1,a1,a1 and it makes sense because the positions I get when I tested it was 373,373,373 always giving me the same index.
Please help, thx
-Stefan
March 30th, 2011 at 9:35 pm
Hi,
Nevermind figured out my issue, but I will leave it here in case anyone else will have the same issue. I used column() at the end of the formula instead of column(a1). This will give you the number of the current column though so you will have to adjust by adding or subtracting.
Thx,
Stefan
March 31st, 2011 at 7:36 am
Stefan,
How to use this array formula:
1. Type:
in cell B25.
2. Press CTRL + SHIFT + ENTER
3. Select cell B25
4. Copy cell B25. (Ctrl + c)
5. Paste it to the cells below as far as needed. (Ctrl + v)
If you don´t follow these instructions, the relative cell reference in ROW(A1) won´t change and will use cell reference a1 in all cells.
April 11th, 2011 at 3:55 pm
Hello Oscar,
LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, I have to match a client name, and add all the sales totals:
clientA 10
clientA 10
clientA 10
clientB 5
clientB 5
clientB 5
So if I search for clientA, I need one cell that keeps a running total as sales are added. Lastly, a date range will need to be given, so search for sales from clientA between two dates and keep a running total...
Any chance you could help me out?
Thank you so much!!
~Andrew
April 12th, 2011 at 9:18 am
Andrew,
Read: Running totals within date range in excel
Thanks for a great question!
May 24th, 2011 at 9:36 am
Oscar,
I am trying to use your formula and adjust it so that more rows can be selected, but get a #VALUE error. I've changed the references to include more than 16 rows, but then I get the error. Here is the equation:
=INDEX($C$3:$C$20,SMALL(IF($H$3=$B$3:$B$20,ROW($B$3:$B$20)-MIN(ROW($B$3:$B$20))+1,""),ROW(A4)))
All that seems to have changed is extending the array and search parameters. Can you help? I like what you have done and it seems to be ingenious. Thanks!
-S
May 24th, 2011 at 9:44 am
Sim,
try:
=INDEX($C$3:$C$20,SMALL(IF($H$3=$B$3:$B$20,ROW($B$3:$B$20)-MIN(ROW($B$3:$B$20))+1,""),ROW(A1)))
ROW(A1) contains a relative cell reference, when you copy the cell (not the formula!), the relative cell reference changes.
May 27th, 2011 at 8:30 am
Oscar,
Using the formula above still gives me the #Value error - and I noticed this. When I try to edit the formula in the formula bar, even if I make no changes, just put the cursor there and click out, it changes from a functional formula to a #Value error. The {} at either end of the formula disappear and when I try to reinsert them, it just gives me this formula in the field:
{=INDEX($C$3:$C$16,SMALL(IF($H$3=$B$3:$B$16,ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1,""),ROW(A1)))}
Am I missing a formatting issue?
May 27th, 2011 at 9:13 am
Sim,
It is an array formula.
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
June 15th, 2011 at 3:30 pm
Awesome work Oscar,
I just have one question, i'm using this formula:
=IF(INDEX(Contactos!$CI$2:$CI$2445;SMALL(IF(Contactos!$CE2=Contactos!$CE$2:$CE$2445;ROW(Contactos!$CE$2:$CE$2445)-MIN(ROW(Contactos!$CE$2:$CE$2445))+1; ""); ROW($A$1)))=AP$1;"X";"") + CTRL + SHIFT + ENTER
My excel is similar to this:
A B C D E F
Names
And in the $CI$2:$CI$2445 range, there are some repeated names, this formula only populates one (A B C D E F), the first it finds, how can i put this to populate more ?
Thanks!
June 16th, 2011 at 12:42 pm
Rui Costa,
Thanks!
Can you describe what the formula is supposed to do in greater detail?
June 16th, 2011 at 2:42 pm
I'm using Excel 2007 and I have 2 Sheets:
Sheet1:
____A B C D E F
bla X X X
ble X X
Sheet2:
Name Letter1 Letter2
bla___A______B
ble___A______C
bla___B______A
I'm trying to make a formula to put in the cell where is the first X after bla, that checks the Sheet 2 for name and populates with the X the letters it finds..
June 17th, 2011 at 1:17 pm
Rui Costa,
read this post: Merge matching rows in excel (text values)
June 20th, 2011 at 8:05 pm
Hi.This array formula is great job and works fine for me,but when cell is empty it give's me an error #NUM.How do i stop this error?These cells need to be fill in later in a month.
Thanks.
June 21st, 2011 at 7:25 am
Hi Oscar, great explaination!!!
I am having a scenario here, lets say I have a company list who sell many brands of products.
Company Products
abc Siemens Omron Mitsubishi
qwe Omron Siemens
asd Omron Moeller
zxc Mitsubishi Omron
I would like to key in the product, then it shown relevant companies. The example shown is great for my first column of products. But for the whole array how is it gonna works?
Thank you
June 21st, 2011 at 8:37 am
Tony R,
Excel 2007, remove #NUM:
wackyboy,
I am not sure I understand. I think this post has an answer for you:
Merge matching rows in excel (text values)
June 21st, 2011 at 8:45 am
Hi Oscar,
I am not looking for merging match rows.. My database is as shown, where I have company abc sells siemens, omron and mitsubishi and company qwe sells omron n siemens. So right now I wanna key in the product name, lets say siemens, I hope it shows all the companies that sell siemens, in this case would be abc n qwe. How is it possible? Using your formula can only works for first column, it will shows company abc only (first column), how to make it seach through the second column n show company qwe as well?
Thank you so much.
June 22nd, 2011 at 9:17 am
wackyboy,
read this post: Vlookup a range in excel
July 13th, 2011 at 9:56 pm
Hi Oscar,
Is there a way I can edit the formula so the #NUM! will be displayed as blank. The column I have consist of client's name and it would look nicer if it is blank instead of the 0.
Thanks a bunch!
July 13th, 2011 at 10:02 pm
Sorry Oscar I have another question. I think this is impossible but is there a way we can do it so we don't need to copy and paste the formula into all the cells we want the items to appear in? I have trying to create a list that will lookup the customer name when I enter the branch number. However, I know one branch would have over 1000 customers in a particular year so I am just wondering if there is a way I can avoid to copy and paste the formula 1000 times.
Thanks again!
July 14th, 2011 at 3:13 am
Hi
I like your tutorial but I was wondering if you might be able to help me out with a problem. Your tutorial is very close to what I want but I can't quite figure out the last bit on my own.
lets say I have three kinds of apples A,B,C flavors. I guess you could say I went out and weighed all the apples I picked from my orchard and noted this down with their flavor. I want to work out the average weight of the apples for each flavour. So I need to go down the column that identifies them as A,B or C pick out only one flavour, lets say all the A's, then averages the weights of the apples for flavour A. The weights of the apples are stored in another column. I just need the average printed in another cell on another sheet in the work book.
I know it needs something like a loop statement and an array but I can't quite seem to get the pieces to fit together. If you can help that would be fantastic. hopefully I explained it well enough for you to understand.
July 14th, 2011 at 2:22 pm
Crystal,
#1
IFERROR(value;value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself otherwise
Example:
#2
You don´t have to copy the cell formula one bye one.
Select cell B25
Copy cell B25 (Ctrl + c)
Select cell range B26:B10026 (Type B26:B10026 in name box and then press enter)
Paste (Ctrl + v)
July 14th, 2011 at 2:44 pm
Sarah,
AVERAGEIF(range, criteria, [average_range])
Finds average (average mean) for the cells specified by a given condition or criteria
AVERAGEIF function
July 14th, 2011 at 4:32 pm
Thank you so much Oscar you saved my day!
July 14th, 2011 at 4:56 pm
Hi Oscar, I was able to get the empty rows to show blank but I am still seeing some zeros on my worksheet. On my original spreadsheet with all the clients' info, there is a comment column that would be blank for some clients. When I put it on the new speadsheet with the lookup formula, those fields now shows a 0. Can you please shed some light on how to make them blank. Thanks again for your help.
July 15th, 2011 at 12:13 pm
Crystal,
Remove zeros
July 15th, 2011 at 6:26 pm
Hi Oscar,
Can you please show me how do I integrate the remove zeros formula into this formula:
=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))),"")
Thanks again!
July 17th, 2011 at 7:58 pm
Crystal,
July 20th, 2011 at 7:07 am
Using your original formula I'm trying to figure out how to do wildcard matches. Can you help?
i.e. Search for CAR
Data
----
MART
CART
SCART
TRACK
CARRY
Results: CART, SCART, CARRY
July 21st, 2011 at 6:37 am
EauMan,
July 23rd, 2011 at 12:30 pm
Hey Oscar,
I have got this array formula working thanks to you, but I get these #NUM! error when all my values are extracted, because the formula is ongoing down the page, I want these #NUM! to display nothing, if there is no values left.
Help would be greatly appreciated !!
=INDEX(CBMSCHE,SMALL(IF(($N$2=$G$4:$G$60),ROW($G$4:$G$60)-MIN(ROW($G$4:$G$60))+1,""),ROW(A1)),2) +CTRL +SHIFT + ENTER.
Thanks.
July 23rd, 2011 at 3:21 pm
Val,
Excel 2007:
July 23rd, 2011 at 3:49 pm
Hey Oscar,
THANKS ALOT !!
It works perfect !! But I am trying it at home's laptop which has Excel 2007.
Do you know if it will work on Excel 2003 ???
THANK YOU !!
July 24th, 2011 at 1:48 am
Hey Oscar,
I am trying the same formula at work, but in Excel 2003, and the #NAME? error now shows up. Do you know why it works differently in Excel 2003 & 2007 ??
& is there a possible solution on this?
Thanks again. !!
July 24th, 2011 at 2:08 am
Hey Oscar,
Sorry for the repeated queries.
But I have figured it out.
Here is the formula that worked for me.
=IF(ROWS($A$1:A1)>SUMPRODUCT(--($N$2=$G$4:$G$60)), "", INDEX($H$4:$H$60, SMALL(IF($N$2=$G$4:$G$60, ROW($G$4:$G$60)-MIN(ROW($G$4:$G$60))+1, ""), ROW(A1)))) +CTRL +SHIFT + ENTER
thanks alot for your help !!
August 3rd, 2011 at 6:38 pm
Oscar,
I've been searching all over (to no avail) for a lead on how to return multiple values from one sheet onto a summary sheet but the tough part is that the criteria for the is that the date be "=>"&today(). I want to return any dates from one sheet onto the summary sheet IF those dates equal today or a future date, but not a past date. Any thoughts?
Thanks.
August 3rd, 2011 at 7:03 pm
Oscar, I'm looking for something like this page on your site: http://www.get-digital-help.com/2007/09/01/excel-find-latest-date-in-a-list/ except that I'd like it to return the last ten values, provided those fit the date criteria of >=today. I'd like to do that on a different sheet than the sheet where the original values are, because I will have multiple sheets that I will need to similarly pull the values from onto the summary sheet.
August 4th, 2011 at 8:56 am
Renee,
I believe this post answers your question:
List names whos date has past in excel
Array formula:
Create named ranges or replace with absolute cell references.
How to create an array formula
1.Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2.Press and hold Ctrl + Shift.
3.Press Enter once.
4.Release all keys.
August 4th, 2011 at 6:18 pm
Excellent! This worked like a charm and I was able to use it with a Sheet! reference.
One more question, how do I get rid of the #NUM! errors that show up in the column that doesn't have a value to return (yet)?
Thanks so much.
August 4th, 2011 at 7:02 pm
I got it! Thank you SO much for your help. I have spent hours trying to figure this out.
August 5th, 2011 at 8:12 am
Renee,
I am happy you figured it out.
Here is how to remove #num errors, for those of you who wants to know.
=IFERROR(formula, "")
October 16th, 2011 at 7:16 pm
I have been working on a worksheet and cannot figure out the formula that would work to copy to the other cells and result in the duplicate entries being shown. I want to enter the phone number in the phone column and have the last name and address populate. Can you give me some guidance? Thanks.
A B C E F G H
1 2 3 4
Phone Last Address 330-555-9235 Adams Mary 12 Oak St.
330-555-8099 Adams Michael 44 Oak St.
330-555-8119 Adams Sam 44 Oak St.
330-555-5566 Jones Mary 36 Main St.
330-555-5327 Palmer Michael 67 ShortSt.
330-555-2227 Powers Sara 27 ShortSt.
330-555-7845 Parker Clarence12 Oak St.
330-555-6565 Parker Mary 36 Main St.
330-555-7901 Smith Clarenc 513 Main St
330-555-7901 Miller Sam 517 E. Main
October 17th, 2011 at 10:38 am
Stephanie,
Can you explain this in greater detail?
I have been working on a worksheet and cannot figure out the formula that would work to copy to the other cells and result in the duplicate entries being shown.
October 19th, 2011 at 9:16 am
Stephanie,
read this post: http://www.get-digital-help.com/2011/10/19/excel-array-formula-enter-a-value-in-a-cell-and-instantly-populate-adjacent-cells/
November 29th, 2011 at 4:10 pm
I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles "ON Work" The drop down menu is in cell B50
I then need to be able to look up whatever option is picked in B50 and look up mulitple values from a different worksheet entiled "Panels" the Panels table goes from A1:F80
So if i choose the option 140XL in B50 below it I need all the information from the "panels" sheet that corresponds with 140XL.
The Panels Sheet information is set up like this:
140XL 12025 16" Large
140XL 58625 12" Small
140XL 99951 12" Rear
So i need it to look up 140XL and get the infromation 12025,58625,9951
i have looked all over the interent for help and it's either way too confusing or doesn't deal with multiple worksheets. I know it is possible but i really am our of ideas
December 1st, 2011 at 6:00 pm
I am trying to use the formula for a bigger range, C3:C40. I modified the formula as follows -
=INDEX('Resource Project Mapping'!C3:C40,SMALL(IF($B$4='Resource Project Mapping'!B3:B40,ROW('Resource Project Mapping'!B3:B40)-MIN(ROW('Resource Project Mapping'!B3:B40))+1,""),ROW('Resource Project Mapping'!A1)))
however I am getting the below results
#VALUE!
#NUM!
#NUM!
#NUM!
#NUM!
Can you please help?
December 5th, 2011 at 10:46 am
DP,
You can find my answer here: How to return multiple values using vlookup
December 5th, 2011 at 3:02 pm
hi,
I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles "ON Work" The drop down menu is in cell B50
I then need to be able to look up whatever option is picked in B50 and look up mulitple values from a different worksheet entiled "Panels" the Panels table goes from A1:F80
So if i choose the option 140XL in B50 below it I need all the information from the "panels" sheet that corresponds with 140XL.
The Panels Sheet information is set up like this:
140XL 12025 16" Large
140XL 58625 12" Small
140XL 99951 12" Rear
So i need it to look up 140XL and get the infromation 12025,58625,9951
i have looked all over the interent for help and it's either way too confusing or doesn't deal with multiple worksheets. I know it is possible but i really am our of ideas
Please Help!
December 7th, 2011 at 11:40 am
Ainslie,
read this post:
Use a drop down list to search and return multiple values
February 13th, 2012 at 7:28 am
Hi Oscar,
Did you post a thread regarding INDEX/MATCH with Multiples criteria?
mean to say, one index range and one match range , but with two or more values to consider to return equivalent index?
ex: column A with various brands (index) and column B with corresponding types of car (sedan, coupe, suv, van...).
query in column C asking for brands having sedan & coupe or sedan & coupe & suv would return corresponding brands.
Thanks to advise thread where is was posted.
February 13th, 2012 at 9:25 am
Oscar,
sorry, I got it:
IFERROR(INDEX(INDEX RANGE,SMALL(IF(ISNUMBER(MATCH($MATCH RANGE,CRITERIA RANGE,0)),ROW(INDEX RANGE)-ROW($C$2)+1),ROWS(LOCATION OF QUERY))),"")
{IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(MATCH($C$2:$C$10,$I$1:$I$2,0)),ROW($A$2:$A$10)-ROW($C$2)+1),ROWS($I$3:I3))),"")}
I changed location of match to column C and formula is in column I just under the two (2) criteria.
However... i still get redondant feedback from index range... have a look if you can:
http://uploading.com/files/m4613588/oscar.xlsx/
February 13th, 2012 at 12:35 pm
Cyril,
I think this is the post you are looking for:
Vlookup with 2 or more lookup criteria and return multiple matches in excel
February 14th, 2012 at 2:31 am
Oscar,
Thanks for the reply, however I end up with the same situation as with my formula (posted on feb 13.
In the file I uploaded, have a look at column I, I placed a note for you there with a specific example.
I still get redundant (double) entries from the index... i'll try to add a condition to the formula, please have a look when you can spare some time, you might have (definitely) a better solution.
Cheers,
Cyril.
February 20th, 2012 at 8:07 pm
Thanks for this formuls
This is working nice
but i need some different than because in my data is with ,
like below
please help me if there is any formula to find from this kind of data
700-AT-003,700-AT-004,700-AT-005,700-AT-006 IAN010061113
700-AT-005 IAN020000001
700-AT-005 IAN020000004
700-AT-004 IAN020000013
700-AT-003 IAN020000015
700-AT-003, IAN020000017
700-AT-006 IAN000036102
700-AT-005 IAN000036103
700-AT-006 IAN000041102
700-AT-003,700-AT-004,700-AT-005,700-AT-006 IAN020000010
700-AT-004 IAN010061104
thanks
February 23rd, 2012 at 10:37 am
Oscar,
Allow me to say thanks for sharing this info.
lucky that I found this & save me a lot of effort.
I took over 2 hrs to understand the original one,
at the I finally understand the whole logic & able to modified the formula to suit my purpose.
this is my latest version of the formula based on your original sharing:
-------------------------------------------------------------------
=IF(ISERROR(INDEX(PMO_Prj,SMALL(IF(($C$1=Project_Status),ROW(Project_Status),""),ROW($A1)),COLUMN(Date_Esculate_to_MDM))),"",IF(INDEX(PMO_Prj,SMALL(IF(($C$1=Project_Status),ROW(Project_Status),""),ROW($A1)),COLUMN(Date_Esculate_to_MDM))=0,"",INDEX(PMO_Prj,SMALL(IF(($C$1=Project_Status),ROW(Project_Status),""),ROW($A1)),COLUMN(Date_Esculate_to_MDM))))
-------------------------------------------------------------------
with some of the column associate with Name (in this case, "Date_Esculate_to_MDM" )
of which this would allow the cell to refresh with accord to the user select in cell [C1], plus show blank when cell either contain nothing or caused error.
allow me to say thanks again for sharing this.
March 8th, 2012 at 10:43 pm
Just one quick question. What does the final ROW A1 do for the formula? I have utilized this formula (thank you, it's amazing) for looking up data from one column to the next. It's great for data pulls where I want the output to be dynamic. I use this formula to filter information. I highlight A:A through C:C with the column headers included, and for some reason the column header came back. When I changes the A1 to A2, it returned the next value. Did I do something wrong?
March 12th, 2012 at 3:48 pm
Alicia,
What does the final ROW A1 do for the formula?
The array formula returns a single value from cell range $C$3:$C$16. The small function returns the k-th smallest number. Cell reference A1 in ROW(A1) is a relative cell reference. It changes when you copy the array formula.
Examine the formula in a few cells and see how the relative cell reference changes. If it didn´t change, the same value would be returned in all cells.
I highlight A:A through C:C with the column headers included, and for some reason the column header came back
You need to adjust the cell references and calculating each cell in column A:A or C:C makes the formula really slow. Try a smaller range. Can you provide your formula?
March 12th, 2012 at 4:22 pm
Hi, I know this isn't array formula but maybe you can still help me. I have a spreadsheet that I use for 3 different companies.
What i would really like to do is have a drop down menu with the three company names: eg: Mcdonalds, Pizza Hut, Subway and then when i choose which company the spreadsheet will be for then all the contact information and logo will appear as a header on the top of the spread sheet. is this possible?