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
Related posts:
Unique distinct values from multiple columns using array formula
Lookup with multiple criteria and display multiple unique search results (array formula)
Return multiple values if in range in excel
Return multiple values if above frequency criterion in excel
Recommended categories
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?
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!
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
This is an array formula, I think you forgot to press Ctrl + Shift + Enter.
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/
In the top example, with data in vertical columns,is it possible to position the output horizontally (vs. vertically), next to the query?
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/
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! ?
nevermind, I just fixed it with a IF ISERROR thanks anyway though, great example and thanks for posting it
Do you suppose you could post that formula with the IF ISERROR parameters included?
Thanks
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.
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
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.
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
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.
They all say #num!? Am i missing a step or formatting?
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?
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
Josh,
You can email me, click "Contact" on the website menu.
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
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
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
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.
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
Can you describe your problem in greater detail?
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)))}
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?
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.
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
Presto! Wish I had found you earlier & saved so many hours! Thanks so much for lending your expertise.
Greg
=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!!!
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)))
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!!!!
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.
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.
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?
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).
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?
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)))
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
this was like returning multiple values, but in a columns not in a row....
john,
read this post: Merge matching rows in excel
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,
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.
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.
A big thanks Oscar, I'd downloaded it and will try to understand that..you're awesome...keep up...
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
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
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.
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
Andrew,
Read: Running totals within date range in excel
Thanks for a great question!
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
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.
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?
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.
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!
Rui Costa,
Thanks!
Can you describe what the formula is supposed to do in greater detail?
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..
Rui Costa,
read this post: Merge matching rows in excel (text values)
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.
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
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)
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.
wackyboy,
read this post: Vlookup a range in excel
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!
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!
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.
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)
Sarah,
AVERAGEIF(range, criteria, [average_range])
Finds average (average mean) for the cells specified by a given condition or criteria
AVERAGEIF function
Thank you so much Oscar you saved my day!
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.
Crystal,
Remove zeros
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!
Crystal,
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
EauMan,
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.
Val,
Excel 2007:
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 !!
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. !!
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 !!
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.
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.
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.
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.
I got it! Thank you SO much for your help. I have spent hours trying to figure this out.
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, "")
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
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.
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/
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
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?
DP,
You can find my answer here: How to return multiple values using vlookup
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!
Ainslie,
read this post:
Use a drop down list to search and return multiple values
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.
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/
Cyril,
I think this is the post you are looking for:
Vlookup with 2 or more lookup criteria and return multiple matches in excel
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.
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
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.
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?
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?
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?
So I'm needing to take the results from column D and *2 to be the results for column E and I can't figure that out. Any help would be appreciated on how to create the formula. I have over 1226 lines with the header making it 1227.
Oscar, you have gotten me much closer to my goal than any other formula I have tried, but I'm still missing something.
My Workbook A is macroed to open two other workbooks, then pulls the value from a specific column from workbook B (Part Numbers) and inserts them into Workbook A Sheet 1, then it counts the number of times the Part Numbers that were inserted into workbook A from workbook B are contained in Workbook C (workbook C contains the Part Numbers and the corresponding Document Numbers assighned to the part when it was orderd in seperate columns) Next I need Sheet 2 of Workbook A to show all the different Document Numbers that have been assighned to the specific part numbers that were pulled into Workbook A. (when the same part is ordered multiple times it is assighned a different document number for each occurance) From the results shown on Sheet 2 of Workbook A, I will make a dropdown list of all the different document numbers next to the corresponding part number on Sheet 1 of Workbook A.
I have referenced the top left cell in Sheet 2 of Workbook A to the first Part Number on Sheet 1 of Workbook A, In the next column on Sheet 2 of Workbook A I have inserted your Formula from your example and changed it accordingly, However when I copy and paste this formula into the rows under it, (hoping to get the next Document Number) I get the same Document Number as the first row that this Formula is placed in. The formula as I have it now is...
{=INDEX(Reportdcr.xls!$A$6:$A$2000, SMALL(IF($A$1=Reportdcr.xls!$E$6:$E$2000, ROW(Reportdcr.xls!$E$6:$E$2000)-MIN(ROW(Reportdcr.xls!$E$6:$E$2000))+1, ""), ROW(A1)))I thank you in advance for any help you or anyone on this thread may be able to provide.
So this is the situation. I am trying to make a new spreadsheet but use formulas from the old spreadsheet. so i'm copying and pasting the old formula into my new spreadsheet. The only thing that changes between the two of them is the name of the worksheet. so below it;s called "sales order form dealer" while the new one is called "Sales Order Form"
The problem is when i paste it into the new spreadsheet it doesn't work. It's supposed to take the information from the "Options" sheet and reference A9 on the Dealers Page.
So that means it looks at A9 on the dealers page - goes to the Options page and finds the A9 Value - located along A1-A900 and then it's supposed to take the B2-B900 Value. So in Options in the A coloum there are a bunch of numbers 120BC 500BC and each has about 10 cells that it occupies then along the rows it has options that relate to the corresponding numbers in the A coloum going down.
The old spreadsheet and new spreadsheet are identical in this way. So i do not know how it isn't working. I put in the formula and in A9 it's a drop down menu so i choose the one i want - ex 420BCL and it should bring me the Options for the 420BCL found in the B coloum in the Options sheet. But it doesn't. It gives me the A coloum in the options sheet.
Does this make sense?
Why doesn't it work the exact same way?
=IF(ISERROR(INDEX(OPTIONS!$A$1:$B$868,SMALL(IF(OPTIONS!$A$1:$A$868='SALES ORDER FORM - DEALER'!$A$9,ROW(OPTIONS!$A$1:$A$868)),ROW(OPTIONS!1:1)),2)),"-",(INDEX(OPTIONS!$A$1:$B$868,SMALL(IF(OPTIONS!$A$1:$A$868='SALES ORDER FORM - DEALER'!$A$9,ROW(OPTIONS!$A$1:$A$868)),ROW(OPTIONS!1:1)),2)))
sorry that code at the bottom is the old spreadhseet formula that i copied and pasted into the new sheet.
Number 2 below (bolded) makes the INDEX function return values from the second coumn in cell range OPTIONS!$A$1:$B$868. I don´t know why it doesn´t, did you enter the formula as an array formula?
=IF(ISERROR(INDEX(OPTIONS!$A$1:$B$868,SMALL(IF(OPTIONS!$A$1:$A$868='SALES ORDER FORM - DEALER'!$A$9,ROW(OPTIONS!$A$1:$A$868)),ROW(OPTIONS!1:1)),2)),"-",(INDEX(OPTIONS!$A$1:$B$868,SMALL(IF(OPTIONS!$A$1:$A$868='SALES ORDER FORM - DEALER'!$A$9,ROW(OPTIONS!$A$1:$A$868)),ROW(OPTIONS!1:1)),2)))
I tried both i thought.
To enter as array you do shift and enter correct?
CSE or Ctrl + Shift + Enter, not just enter on a PC or Command Return on a mac.
Hi Oscar,
first of all thank you for a great website and taking the time to help novices like me. I am your formula
=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.
What do I need to do if I don't want $P$5 to be absolute but want to use it as $P5 and copy the formula down?
Bert Schenke,
Try this:
=INDEX($M$3:$M$14, SMALL(IF(($P5=$K$3:$K$14)*($P$4=$L$3:$L$14), ROW($K$3:$K$14)-MIN(ROW($K$3:$K$14))+1, ""), COLUMN(A1)))
This array formula returns values horizontally.
Hi Oscar,
thank you so much for your help.
Hi Oscar,
This has been a massive help and I've learnt to arrays! I'm self taught in excel and learn what I need to know but for this solution it looked like learning VB was on the cards (maybe another day).
My situation was that I had individual items sold entered one line each in a sheet (job sheet) but a customer may buy 3 items and so to generate the invoice I had a cell on the invoice template to enter invoice number and return everything needed from that line hence the multiple lookup.
All fine until.... As customer names (for the vlookup of their address) and description of products (for stock control sheet) need to be exact I had drop downs in the cells. Now when I reopen them these have gone, I have tried re doing the sheets, going step by step to see what action stops them but to no avail.
Should I email a copy of the workbook?
Hope you can help, I see so many people asking questions you have already answered,so I read through the posts and got all I needed and so hoped not to trouble you but this has me stumped.
Liam
Liam,
Should I email a copy of the workbook?
Yes, please. Without any sensitive data. Use this contact form.